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

Question

Level 1

4 points

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

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

Initial spreadsheet

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.

iPad 2, iOS 5.0.1

Posted on Feb 20, 2012 7:37 PM

Reply

Answer 1

Jeff Shenk

Level 4

2,993 points

Feb 21, 2012 4:48 AM in response to rbeckmann

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.

Reply

Answer 2

rbeckmann Author

Level 1

4 points

Feb 21, 2012 3:07 PM in response to Jeff Shenk

Thanks Jeff for your reply.

What you are describing is an "absolute" cell reference, or as Apple iOS refers to it as "Preserve" the row and/or column, indicated by a $ in front of cell/column identifier. You access that using the "triangle" in the formula.

A "relative cell reference", on the other hand, changes its value to reflect its new location. "Relative" is the default action.

I've worked with spreadsheets for many years and confirmed it works as I described it should in both Excel and in Numbers for the MAC.

It is not working correctly in iOS.

Thanks,
Robert

Reply

Answer 3

Jeff Shenk

Level 4

2,993 points

Feb 21, 2012 4:17 PM in response to rbeckmann

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.

Reply

Answer 4

Jeff Shenk

Level 4

2,993 points

Feb 21, 2012 4:31 PM in response to Jeff Shenk

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.

Reply

Answer 5

rbeckmann Author

Level 1

4 points

Feb 21, 2012 5:51 PM in response to Jeff Shenk

Jeff,

Interesting.

I went back and double checked it in Excel (Windows) and Numbers for the Mac.

Excel I was certain of since I have been using it since its initial release in the 80's. It works as I expect. Excel uses the absolute/relative terminolgy I used above.

Now I doubt I actually tested this issue in Numbers on the Mac. It was a month or so ago when I did the test(s). Had put off following up on it until yesterday. I only have a (now expired) trial version of Numbers on the Mac which still allows me to create, but not save, a spreadsheet. I just retested it and I get the same results in Numbers on the Mac as in iOS on the iPad (the same results as you reported).

The acutual spreadsheet I created is more complex than the very simplified example I used. It for organizing and scheduling tasks, planned versus actual time to complete, remaining time for day, scheduled time tasks will be complete, actual time completed, etc. Lots of date math. In this application I want the formulas on a row to remain the same if I reorder the tasks. This is easily done in Excel by either sorting the entire table OR by sorting selected cells (in this case the tasks and not the formulas).

Apparently neither is possible in Numbers.

The work around for me has been to maintain a top row of formulas that are never modified and, each time I resort the rows, copy the top row of formulas to all the other rows.

Thanks for your help on this.

Robert

Reply

Answer 6

Jeff Shenk

Level 4

2,993 points

Feb 21, 2012 6:24 PM in response to rbeckmann

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.

Reply

Answer 7

rbeckmann Author

Level 1

4 points

Feb 21, 2012 7:54 PM in response to Jeff Shenk

Thanks Jeff,

I'll check it out also.

Reply

Answer 8

rbeckmann Author

Level 1

4 points

Feb 21, 2012 9:02 PM in response to Jeff Shenk

OFFSET accomplishes what I need.

Initial formulas

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

These maintain the relative row using OFFSET

Row B 1

2 1 (=B2+OFFSET(C2,-1,0) ) value = 2

3 1 (=B3+OFFSET(C3,-1,0)) value = 3

4 1 (=B4+OFFSET(C4,-1,0)) value = 4

5 1 (=B5+OFFSET(C5,-1,0)) value = 5

The OFFSET function returns a range of cells that is the specified number of rows and

columns away from the specified base cell.

OFFSET(base, row-offset, column-offset, rows, columns)

base: The address of the cell from which the offsets are measured. base is a

reference value.

row-offset: The number of rows from the base cell to the target cell. row-offset is a

number value. 0 means the target cell is in the same row as the base cell. A negative

number means the target is in a row above the base.

column-offset: The number of columns from the base cell to the target cell. columnoffset

is a number value. 0 means the target cell is in the same column as the base

cell. A negative number means the target is in a column to the left of the base.

rows: An optional value specifying the number of rows to return starting with the

offset location.rows is a number value.

columns: An optional value specifying the number of columns to return starting

with the offset location.columns is a number value.

Reply

Answer 9

rbeckmann Author

Level 1

4 points

Feb 21, 2012 9:05 PM in response to Jeff Shenk

Jeff,

Do you know if there is a way for me to set your last response as the
"this solved my question" instead of the earlier one, or this something I need to request of a moderator?

Thanks again for your help.

Robert

Reply

Answer 10

rbeckmann Author

Level 1

4 points

Feb 22, 2012 8:05 AM in response to rbeckmann

One other note: for this issue (same formula on every row, frequent resorting, want to maintain the same relative reference) the BASE element of the OFFSET function nneds to reference the CURRENT row

Reply