[Solved] Sorting on the results of formulas

Discuss the spreadsheet application

[Solved] Sorting on the results of formulas

Postby mhklein57 » Wed Jun 27, 2012 12:49 am

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!
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
mhklein57
 
Posts: 2
Joined: Wed Jun 27, 2012 12:42 am

Re: Sorting on the results of formulas

Postby thomasjk » Wed Jun 27, 2012 2:02 am

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
thomasjk
Volunteer
 
Posts: 4435
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Sorting on the results of formulas

Postby MrProgrammer » Wed Jun 27, 2012 6:52 am

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.
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 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

Re: Sorting on the results of formulas

Postby acknak » Wed Jun 27, 2012 2:48 pm

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.

 Edit: PS: 
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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sorting on the results of formulas

Postby MrProgrammer » Thu Jun 28, 2012 6:28 am

mhklein57 wrote:I want to sort only the numbers in Column C
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:
Code: Select all   Expand viewCollapse view
    A   B   C
1   5   0   =A1-B1
2   5   1   =A2-B2
3   5   2   =A3-B3

So 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.

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.

acknak wrote:sort the data along with the formulas and it should work correctly.
If we change the example to (note that A1 is now =A3-B3 and A3 is =A1-B1)
Code: Select all   Expand viewCollapse view
    A   B   C
1   5   2   =A3-B3
2   5   1   =A2-B2
3   5   0   =A1-B1

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.
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

Re: Sorting on the results of formulas

Postby mhklein57 » Fri Jun 29, 2012 1:36 am

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!
Michael H. Klein OpenOffice 3.2 Windows 7
mhklein57
 
Posts: 2
Joined: Wed Jun 27, 2012 12:42 am

Re: Sorting on the results of formulas

Postby thomasjk » Fri Jun 29, 2012 1:52 am

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
thomasjk
Volunteer
 
Posts: 4435
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Sorting on the results of formulas

Postby jrkrideau » Fri Jun 29, 2012 3:52 pm

MrProgrammer wrote:
mhklein57 wrote:I want to sort only the numbers in Column C
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:
Code: Select all   Expand viewCollapse view
    A   B   C
1   5   0   =A1-B1
2   5   1   =A2-B2
3   5   2   =A3-B3

So 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.

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.

acknak wrote:sort the data along with the formulas and it should work correctly.
If we change the example to (note that A1 is now =A3-B3 and A3 is =A1-B1)
Code: Select all   Expand viewCollapse view
    A   B   C
1   5   2   =A3-B3
2   5   1   =A2-B2
3   5   0   =A1-B1

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.


Excellent description and demonstration of the perils of sorting formulae. It probably should go into a tutorial.

Do you mind if I use it in other places if I supply proper citation and reference?
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3806
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Sorting on the results of formulas

Postby MrProgrammer » Fri Jun 29, 2012 8:39 pm

jrkrideau wrote:It probably should go into a tutorial.
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:Do you mind if I use it in other places if I supply proper citation and reference?
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.
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

Re: [Solved] Sorting on the results of formulas

Postby jrkrideau » Sat Jun 30, 2012 3:18 pm

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.


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.
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3806
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Sorting on the results of formulas

Postby Alex1 » Sat Jun 30, 2012 7:13 pm

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.7 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 608
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests