[Solved] Formulas Don't Adapt to Sorting Their References

Discuss the spreadsheet application

[Solved] Formulas Don't Adapt to Sorting Their References

Postby bluhawk19 » Mon Jul 02, 2018 8:33 pm

As the subject suggests, I want to be able to have formulas keep pulling data from the same source, even if I sort the source data. Forgive me if this sounds amateurish. I'm not very knowledgeable with the formula functions.

For example, say that I have data in the first four cells of column A. A1 contains the digit 3, A2, 5, A3, 1, and A4, 2. In B1, I have =SUM(A1+A2), which is 8, and B2 contains =SUM(A3+A4), which equals 3. Say that I want to sort column A, though, from lowest to highest value. Now, it reads 1, 2, 3, and 5. The formulas, however, do no adapt to this change. I would like them to still equal 8 and 3, as they did before the sort, but now, they read 3 and 8. How do I make sure that formulas still present the same values before and after I sort their source cells?
Last edited by bluhawk19 on Sat Nov 24, 2018 11:29 pm, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
bluhawk19
 
Posts: 6
Joined: Mon Jul 02, 2018 8:24 pm

Re: Formulas Don't Adapt to Sorting Their Reference Cells

Postby RusselB » Mon Jul 02, 2018 11:27 pm

Personally, I don't think you can without forcing your formulas to become fixed values, but this tutorial may have other information.
[Tutorial]Sorting and Filtering with Formulas
Off topic, but to make your spreadsheet more efficient, don't use the SUM function for simple two cell addition.
=SUM(A3+A4) will give the same results as =A3+A4, but the first one requires a little bit more memory, and thus is less efficient.
For larger ranges (eg: A3 to A50 inclusive) then you should use the SUM function, rather than a lot of + characters... an easy rule of thumb, is look at the number of characters required for the formula to be entered, and go with the formula that uses the least number.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6314
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formulas Don't Adapt to Sorting Their Reference Cells

Postby MrProgrammer » Tue Jul 03, 2018 6:04 am

bluhawk19 wrote:How do I make sure that formulas still present the same values before and after I sort their source cells?
Data → Sort → Options → Copy results to → {somewhere else}.

An in-place sort can't preserve values in dependent cells (cells which depend on the values being sorted) because it shuffles the data in ways which may not preserve cell ranges. Suppose the range being sorted is A2:A4999 and cell D5 contains =AVERAGE(A500:A3141). The values which were in one range, A500:A3141, may be broken into a thousand new ranges, say A605, A2047:A2048, A666, A3033, A1414, A905:A906, …. The AVERAGE function can't handle a thousand ranges as arguments. So there is no way Calc can adjust that formula to preserve its value after the sort has rearranged the data.

Maybe P4 contains =VLOOKUP(P3;$A$705:$A$1789;2;0). VLOOKUP needs the lookup range to be a contiguous set of cells. The sort can break A705:A1789 into many pieces. Calc cannot adjust the VLOOKUP formula so it works as before.

Because there are some dependent formulas which can't be adjusted to preserve the values which they had before the sort, the developers would have realized that the only practical way to handle dependent formulas is to adjust none of them.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3911
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests