[Solved] Sorting on the results of formulas

[mhklein57]

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!

[thomasjk]

Are you sure that they are all numbers? Are you just sorting on the one column?

[MrProgrammer]

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.

[acknak]

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.

[MrProgrammer]

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

    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.

sort the data along with the formulas and it should work correctly.

If we change the example to (note that C1 is now =A3-B3 and C3 is =A1-B1)

Code: Select all

    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.

[mhklein57]

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!

[thomasjk]

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.

[jrkrideau]

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

    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.

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

    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?

[MrProgrammer]

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.

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.

[jrkrideau]

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.

[Alex1]

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.