Formula reference errors after sorting

Question

Formula reference errors after sorting

I am trying to sort some data in Numbers (3.2.2) and keep getting reference errors after the sort.

One column has calculated values with formulas that refer to various cells in the same table. After applying a sort to a different column, the formula are completely messed up.

When you sort in Excel it acts as if you simply moved the rows into the desired order. In Numbers you can do that manually and it works fine, but if you try to sort, it appears that it treats any cell references in formulas as relative to the cell containing the formula, rather than moving them with the sort. This essentially means that the sort function is useless if you have formulas that refer to data in any other row besides the row that contains the formula. Am I missing something here?

Below is an attempt at illustrating the issue:

iMac, OS X Mavericks (10.9.5)

Posted on Jan 10, 2015 7:43 PM

Reply

Answer 1

SGIII

Level 8

37,532 points

Jan 11, 2015 9:44 AM in response to P in Minneapolis

Here's a screenshot of the results in Excel:

I would argue that the "Excel way" of sorting is the superior approach in the vast majority of cases, simplifies formulas, and encourages good spreadsheet design.

The old Numbers way of sorting was convenient in a subset of use cases, but, when you think about it, was pretty quirky!

SG

Reply

Answer 2

Jan 11, 2015 8:17 AM in response to Jerrold Green1

Jerry-

Thanks for your reply & help.

Reading your post in the other thread it sounds like it essentially can't be done so what you have to do instead is create auxiliary tables with some convoluted formulas to display the data in the desired order, correct?

I tried the code you posted in reply to Ennoxx and got it to work (never could have come up with that on my own, by the way!) but all I can say is 'Wow' Your comment that "Sorting in Numbers V3 is a mess" was a huge understatement! Their implementation makes it virtually unusable. I can only assume others have already done this, but I'll add my voice to the chorus in the feedback forum.

For anyone else who happens to stumble across this, a quicker and simpler method is:

Create a 2nd table
Copy the cells you wish to sort
Paste only the values (Shift +⌘+V or 'Paste Formula Results' from the Edit menu)
Sort the pasted data in the new table as you desire

This process has to be repeated anytime you change the data, so if you have data that is changing, the code Jerry posted on the other forum provides a way to do it dynamically.

Reply

Answer 3

Jan 11, 2015 8:41 AM in response to P in Minneapolis

P,

Probably the best approach is to revert your document to iWork '09. There, sorting of columns with formulas was much easier.

If you are going to be sorting frequently in Numbers V3, it is worth the time to do the programming changes to work around the sort limitations in Numbers V3.

My expressions are not convoluted. The use of an auxiliary table, in general, keeps things simple. If you can identify a more direct solution, other than switching to Numbers Version 2, I'd like to see it. Using LOOKUP and its sibling reference functions, is a well-recognized approach to being "sort safe".

Here is a case of Apple listening to the users, and being pulled in the wrong direction. In Version 2, many former Excel users were complaining about the sort behavior. Apple changed the sort behavior to be more like Excel, but they obviously didn't get it right.

Jerry

Reply

Answer 4

Jan 11, 2015 1:09 PM in response to Jerrold Green1

Jerry-

Please don't take my comments as a criticism of your programming - they were in no way intended as such. Rather, they were a reflection of how difficult it is to do something that should be rather simple. I tried saving the document as a Numbers '09 document, but the sorting behavior appears to be the same.

SG - I tried putting the example data I posted Excel and indeed got the results you posted, which is curious as I tried sorting the data in Excel before making my original post and it seemed to sort as I expected. Perhaps I should amend my original comments on Excel's behavior.

I have to agree with Jerry that Apple was pulled in the wrong direction. It appears that Numbers (and Excel?) maintains formula cell references in a relative fashion than adjusting them as the target cells are moved with the sort. If this is your goal it's easier to program this into a formula for the times you need it than to program it out of them when you don't.

For the times I need to sort data involving formulas I'll either copy and past values in a separate table to sort or use Jerry's technique.

Reply

Answer 5

Jan 11, 2015 3:40 PM in response to P in Minneapolis

P,

I don't take your comment personally, but if you take the time to follow the logic, you will see that it is a simple progression. Practice using the LOOKUP function and you will see how much power there is in a function with such simple syntax. It's no more complicated than remembering how to rotate you car's tires. 🙂

Good luck,

Jerry

Reply

Answer 6

Jan 10, 2015 8:36 PM in response to P in Minneapolis

P,

I just posted a response to a very similar question in this other discussion. Read to the bottom of the thread.

Jerry

Reply