## Sort does not maintain relative row when using a formula referencing another row

639 Views 10 Replies Latest reply: Feb 22, 2012 8:05 AM by rbeckmann
Calculating status...
Currently Being Moderated
Feb 20, 2012 7:49 PM

I added this same information in a bug report, but am adding it here as well since the latest version of the iOS to select from when reporting the bug was 4.3; yet version 5 has been available for months; and the lastest version of Numbers for iOS to select from was 1.4, while my version is 1.5 (423). Makes me wonder if/how often they look at the bug reports.

------------------------------------

Bug Report

Sort does not maintain relative row when using a formula referencing another row (the row above in this example)

PRESERVE ROW AND COLUMN ARE TURNED OFF FOR ALL FORMULAS

When resorted by changing a value in Column A,  the formulas should maintain  their relative row values. THEY DO NOT.

The sort and formulas work correctly in Numbers for  the Mac, but not in Numbers for iOS.

In the following example:

Row 1 is the header row

Column  A is used to sort

Column B contains the numeral  1 in all rows

Column C contain the numeral 1 in Row 1 and a formula in the remaining rows. The formula adds the value in value in column C in the prior row to the value in column B in the current row (i.e. to keep a running total).

Row       B             1

2              1           (=B2+C1 ) value = 2

3              1           (=B3+C2)  value = 3

4              1           (=B4+C3) value = 4

5              1           (=B5+C4) value = 5

In this example the value 4 in column A is changed to 2.5 and the table is resorted.

What you SHOULD get after sorting is this (since no rows or columns are preserved).

Row       B             1

2              1          (=B2+C1 )  value = 2

2.5           1          (=B3+C2)  value = 3

3              1          (=B4+C3) value = 4

5              1          (=B5+C4) value = 5

What you ACTUALLY get after sorting is this:

Row       B             1

2             1           (=B2+C1 )  value = 2

2.5          1           (=B3+C4)  value = 4 SHOULD BE B3+C2

3             1           (=B4+C2)  value = 3 SHOULD BE B4+C3

5             1           (=B5+C3)  value = 5 SHOULD BE B5+C4

The version of Numbers I am using, 1.5(423), is not available in the drop down list to select from. The latest in the drop down is 1.4.

The version iOS I am using, 5.0.1(9A405), is also not available in the drop down list. The latest in the drop down is 4.3.

That these releases have been out for four months and are not available in the drop down makes me wonder if/how often these bug reports are looked at.

Message was edited by: rbeckmann I'm leaving this marked as a question in case i have missed something, but I don't think i have since i have confirmed it works as I described it should in both Excel for Windows and in Numbers for the Mac.

• Level 4 (1,890 points)

I think that is working correctly, but you are misunderstanding what a relative reference is.

It does not simply specify an offset to another cell; it specifies a reference to a specific cell. If you copy and paste the formula, the new formula will point to a cell at the same offset from its location as the original formula, but if you move the original formula, it still points to the original referred cell (that is, the offset changes).

Looking at your Row 4, which the sort moves to Row 3, the sort switches rows 3 and 4, so the relative references for 3 and 4 have to switch: = B4 + C3 becomes = B3 + C4. B3 now refers to the same cell that used to be at B4 and C4 now refers to the same cell that used to be at C3.

And that is probably totally confusing; I know what i'm trying to say, but I'm probably not saying it very clearly. Basically, you don't want a relative reference, you want a fixed offset.

• Level 4 (1,890 points)

No, you don't understand what absolute and relative references are.

An absolute cell reference will always point to the same cell, no matter how it is copied or moved around. If you drag that reference cell to a new location, the formula will change to point to the new location, because the reference is attached to the cell, not the location.

A relative will also always point to the same cell if you move the formula to a different cell, but if you copy the formula and paste it in another cell (or fill down or sideways, which are basically also copy operations), the copy will point to the cell at the same relative position to the copied formula as the original reference was to the original formula.

Sorting is a move operation, so it is working correctly, and I have been checking this on Numbers on my Mac which does behave this way. I don't have Excel, so I can't check on that, but your example sorts exactly the same on the Mac as it did on iOS.

• Level 4 (1,890 points)

I just checked on LibreOffice, and it appeared to sort the way you expect (but I am not really familiar with it; it's the first time I have tried it, so I may have done something wrong). So, I expect that Excel does work the way you expect.

I prefer the way Numbers allows you to move cells without having to rewrite the references, but I can see that if you are used to the other way it could be a problem.

• Level 4 (1,890 points)

I believe you can accomplish what you want in Numbers using the OFFSET function, but I haven't had time to try it out. Jerrold Green, Barry, Badunit and Wayne Contello on the main Numbers community are all better than I at working with these functions.

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.