Currently Being ModeratedFeb 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.
Currently Being ModeratedFeb 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.
Currently Being ModeratedFeb 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.
Currently Being ModeratedFeb 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.
Currently Being ModeratedFeb 21, 2012 5:51 PM (in response to Jeff Shenk)
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.
Currently Being ModeratedFeb 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.
Currently Being ModeratedFeb 21, 2012 7:54 PM (in response to Jeff Shenk)
I'll check it out also.
Currently Being ModeratedFeb 21, 2012 9:02 PM (in response to Jeff Shenk)
OFFSET accomplishes what I need.
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
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.
Currently Being ModeratedFeb 21, 2012 9:05 PM (in response to Jeff Shenk)
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.
Currently Being ModeratedFeb 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