[Solved] Sorting on the results of formulas
[Solved] Sorting on the results of formulas
I have a spreadsheet with three columns. In Column A are ten numbers. In Column B are ten other numbers having no relationship with those in Column A. In Column C is the formula = Ax - Bx where x is the particular row (first row has = A1 - B1 and so on). I want to sort only the numbers in Column C. When I use the usual sort function, the numbers aren't sorted properly. What do I do?
Your attention to this matter will be very much appreciated! Thanks!
Your attention to this matter will be very much appreciated! Thanks!
Last edited by mhklein57 on Fri Jun 29, 2012 1:37 am, edited 1 time in total.
Michael H. Klein OpenOffice 3.2 Windows 7
Re: Sorting on the results of formulas
Are you sure that they are all numbers? Are you just sorting on the one column?
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sorting on the results of formulas
Hi, and welcome to the forum.
Since you're using OOo 3.2, from a practical standpoint, it isn't possible to sort cells containing formulas. You must only sort cells that contain data. So, before sorting column C, you must convert the formulas to their values, which in this case is easily done with Edit > Copy then Edit > Paste Special > Numbers (only). The problem is described in more detail in Sort does not work in sheets with vertical cell-references. I'm very sure your problem is the same as that one, even if the symptoms seem different. Calc is adjusting the references as the data is rearranged. That isn't what you want, but that's the way that Calc works. So you must eliminate the references by converting the formulas to data.
If this answered your question 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.
Since you're using OOo 3.2, from a practical standpoint, it isn't possible to sort cells containing formulas. You must only sort cells that contain data. So, before sorting column C, you must convert the formulas to their values, which in this case is easily done with Edit > Copy then Edit > Paste Special > Numbers (only). The problem is described in more detail in Sort does not work in sheets with vertical cell-references. I'm very sure your problem is the same as that one, even if the symptoms seem different. Calc is adjusting the references as the data is rearranged. That isn't what you want, but that's the way that Calc works. So you must eliminate the references by converting the formulas to data.
If this answered your question 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.
Last edited by MrProgrammer on Fri Jun 29, 2012 8:45 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Sorting on the results of formulas
I'm not sure this is quite so complex as the case MrP links to.
Here, each row of the table is self-contained; there are no formulas between different rows as in the more complex case.
When the formula only uses data from it's own row, you can easily sort the results if you also include all the data in the same operation.
With your example: click in column C, then click the "A..Z" button on the toolbar.
Those instructions only work with newer versions. For anything older, you have to select all the cells yourself and use Data > Sort to get all the options right. The point is the same: sort the data along with the formulas and it should work correctly.
Here, each row of the table is self-contained; there are no formulas between different rows as in the more complex case.
When the formula only uses data from it's own row, you can easily sort the results if you also include all the data in the same operation.
With your example: click in column C, then click the "A..Z" button on the toolbar.
Edit: PS: |
AOO4/LO5 • Linux • Fedora 23
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sorting on the results of formulas
I'm assuming that statement means that the OP is using "Current selection" instead of "Extend selection" when prompted (The cells next to the current selection also contain data …). Since the OP didn't supply details about how "the numbers aren't sorted properly" (what happened? and what was expected?) nor any sample data, I had to make my own assumptions about the problem. So I created:mhklein57 wrote:I want to sort only the numbers in Column C
Code: Select all
A B C
1 5 0 =A1-B1
2 5 1 =A2-B2
3 5 2 =A3-B3
Here's what happened. Calc looks at the values 5, 4, and 3 and decides that to be in ascending order C1 should move to C3, C2 can stay where it is, and C3 should move to C1. So formula =A1-B1 in C1 will be moved two rows down to C3 and all the references in it will be adjusted accordingly (two rows down) thus becoming =A3-B3. And formula =A3-B3 that was in C3 before the sort will be moved two rows up to C1 and its references adjusted two rows up to become =A1-B1. Thus the new contents of C1:C3 are =A1-B1, =A2-B2, and =A3-B3, exactly same as before the sort. So the sort was performed but the values shown for those cells remain 5, 4, and 3 even though the cells were sorted in ascending order.
Other tests on OOo 3.2 with different formulas show results consistent with the explanation of how formulas sort given in the Sort with inter-row references topic. Because it will be difficult for users to predict how row references will be adjusted when the user tries to sort formulas, I believe the only practical method is to convert formulas to values before sorting.
It's possible that OOo 3.3, AOO 3.4, LibreOffice, etc. handle formula sorting differently, however I don't see anything about sorting changes in the release notes, and I am assuming that their behavior is the same as OOo 3.2 in this situation. Perhaps others can confirm or refute the assumption that other releases sort the formulas in my example as I have described.
If we change the example to (note that C1 is now =A3-B3 and C3 is =A1-B1)acknak wrote:sort the data along with the formulas and it should work correctly.
Code: Select all
A B C
1 5 2 =A3-B3
2 5 1 =A2-B2
3 5 0 =A1-B1
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Sorting on the results of formulas
Mr. Programmer, thank you so much! You recreated my problem exactly and came up with just the solution I needed. Kudos to you!
I'm just surprised that more people haven't encountered this problem and that it is not better documented in OpenOffice help.
Again, thanks for your help!
I'm just surprised that more people haven't encountered this problem and that it is not better documented in OpenOffice help.
Again, thanks for your help!
Michael H. Klein OpenOffice 3.2 Windows 7
Re: Sorting on the results of formulas
Libreoffice 3.5.4 handles the sorting correctly. References are adjusted correctly. I believe V3.3 and also V3.4 would also work correctly. i don't have either of those installed to test with.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Sorting on the results of formulas
Excellent description and demonstration of the perils of sorting formulae. It probably should go into a tutorial.MrProgrammer wrote:I'm assuming that statement means that the OP is using "Current selection" instead of "Extend selection" when prompted (The cells next to the current selection also contain data …). Since the OP didn't supply details about how "the numbers aren't sorted properly" (what happened? and what was expected?) nor any sample data, I had to make my own assumptions about the problem. So I created:mhklein57 wrote:I want to sort only the numbers in Column CSo C1:C3 has values 5, 4, and 3. With OOo 3.2, when I select C1:C3, then Data > Sort > Current selection (using the default Ascending Sort), I find that after the sort, C1:C3 still has =A1-B1, =A2-B2, and =A3-B3 with values 5, 4, and 3!! This is rather unexpected, but is a natural consequence of the behavior discussed in the link provided in my last post.Code: Select all
A B C 1 5 0 =A1-B1 2 5 1 =A2-B2 3 5 2 =A3-B3
Here's what happened. Calc looks at the values 5, 4, and 3 and decides that to be in ascending order C1 should move to C3, C2 can stay where it is, and C3 should move to C1. So formula =A1-B1 in C1 will be moved two rows down to C3 and all the references in it will be adjusted accordingly (two rows down) thus becoming =A3-B3. And formula =A3-B3 that was in C3 before the sort will be moved two rows up to C1 and its references adjusted two rows up to become =A1-B1. Thus the new contents of C1:C3 are =A1-B1, =A2-B2, and =A3-B3, exactly same as before the sort. So the sort was performed but the values shown for those cells remain 5, 4, and 3 even though the cells were sorted in ascending order.
Other tests on OOo 3.2 with different formulas show results consistent with the explanation of how formulas sort given in the Sort with inter-row references topic. Because it will be difficult for users to predict how row references will be adjusted when the user tries to sort formulas, I believe the only practical method is to convert formulas to values before sorting.
It's possible that OOo 3.3, AOO 3.4, LibreOffice, etc. handle formula sorting differently, however I don't see anything about sorting changes in the release notes, and I am assuming that their behavior is the same as OOo 3.2 in this situation. Perhaps others can confirm or refute the assumption that other releases sort the formulas in my example as I have described.
If we change the example to (note that A1 is now =A3-B3 and A3 is =A1-B1)acknak wrote:sort the data along with the formulas and it should work correctly.we get #REF! errors when we sort column C (values 5, 4, 3), whether we use Current Selection (column C only) or Extend Selection (sort data along with column C). =A1-B1 is moved to C1 and its references are adjusted two rows up, causing the error.Code: Select all
A B C 1 5 2 =A3-B3 2 5 1 =A2-B2 3 5 0 =A1-B1
Do you mind if I use it in other places if I supply proper citation and reference?
LibreOffice 7.3.7. 2; Ubuntu 22.04
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [Solved] Sorting on the results of formulas
I'll consider that, however I should probably upgrade to AOO 3.4 first, since comments in this topic suggest that the sorting of formulas may have changed in later releases and it is perhaps not so useful to create a tutorial which does not apply to OOo 3.3 or AOO 3.4 or LibreOffice. I've been thinking of upgrading, but am waiting to see what sorts of issues are being reported for 3.4. I know 3.3 had several printing issues, especially printing envelopes, and since I wasn't interested in any of the 3.3 enhancements I elected to stay with 3.2. But 3.4 has additional enhancements, like a new RegEx engine, and I haven't seen many problems that weren't fixed with a new profile, which I would do anyway. I have all of my profile settings documented externally so I can redo them in a new one.jrkrideau wrote:It probably should go into a tutorial.
I'd be happy to have you reference this topic or the earlier Sort does not work in sheets with vertical cell-references which provides more detailed analysis of the situation. However the eariler topic doesn't specifically state that the analysis was for OOo 3.2.jrkrideau wrote:Do you mind if I use it in other places if I supply proper citation and reference?
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: [Solved] Sorting on the results of formulas
Excellent. I will do so. Do you know if other spreadsheets have such a problem? I have read references to a similar problem in Excel but some years ago.MrProgrammer wrote:I'd be happy to have you reference this topic or the earlier Sort does not work in sheets with vertical cell-references which provides more detailed analysis of the situation. However the eariler topic doesn't specifically state that the analysis was for OOo 3.2.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: [Solved] Sorting on the results of formulas
Imho it's intentional behaviour. It's unusual to sort only certain columns. If you do want to do this, you should write =A$1-B$1 etc.
AOO 4.1.15 & LO 24.2.2 on Windows 10