1

I have a table with a basic formula:

enter image description here

The 7th column (1038€94) is calculated with the formula 1133€34 - 94€40 + (empty cell). In others words, the 7th column is calulated by (value in the row above) - (value of the 4th cell of the row) + (value of the 5th cell of the row).

All the rows have the same formula in the 7th column.

The first column is a date. If I choose to sort the rows depending on this date, the formula breaks because Numbers keeps a pointer to the cells before sorting the data:

enter image description here

Is there a way to sort the rows without breaking the formula? Or make the formula uses the cell above without specifying the line in the formula?

Thanks

Improve this question
0

3 Answers 3

Reset to default
2

I think there is no easy solution to this within Numbers. You should either copy your table to another sheet as values (formula results) whenever you need to sort it or recreate your formulas using, for example, the vlookup() function (also check the hlookup() and other reference functions in Numbers in case necessary) if you need to sort in-place. In the 2nd solution, instead of getting 1133,34 by a direct reference to its cell, you would get it (and any other numbers not at the same row in the formula yielding 1038,94) through a separate "vlookup" each. In addition, all "vlookup"s should be created sort-proof specifically against column-based sorts.

By column-based sort-proofing, I mean the cell references within "vlookup"s should directly refer only to the cells in the same row or the whole table. Then, assuming the formulas in the column where 1133,34 is calculated are parallel, you can use the same formula for all the cells in the same column by copy & pasting it.

Below is an example formula if you wish to take the 2nd option. It simply adds two values from the "some variable" column. The first value is from the current row and the second from two days earlier. Pls note the exact use of absolute cell references in the formula (the $ signs, necessary so that the same formula can be used in other cells in the same column by copy & paste) and how the date for two days earlier is referenced to in the vlookup in the formula.

enter image description here

Here is a copy of the same formula in text in case you have trouble recreating it and wish to copy & paste from here:

= B5+VLOOKUP($A5−2,$A$2:$C$5,2,0)

And here is how the table and the same formula looks like when the table is sorted based on the date:

enter image description here

Improve this answer
1
  • I just noticed your table has multiple rows with the same date. Then the exact solution I suggested may not work for you but maybe you can use some of the ideas in it together with some other solution to formulate something that works for you. Good luck!
    – Alper
    Commented Mar 14, 2020 at 21:45
2

For cells you wish to reference in a formula using their relative position to the current cell, you'll need to construct references based on the row and column number of the current cell. You can then use the INDIRECT() function to get the value of the cell at that reference.

For example, suppose the formula for cell G24 is

= G23 - D24 + E24,

and after sorting rows, this becomes e.g. cell G5, whose formula you then want to be

= G4 - D5 + E5,

regardless of where the original row 23 ends up after sorting. Then you can use the following formula for cell G24:

= INDIRECT( "G" & (ROW()-1) ) - D24 + E24.

Thus, after sorting rows, when G24 becomes e.g. G5, the formula becomes

= INDIRECT( "G" & (ROW()-1) ) - D5 + E5,

which gives you the desired result.

For maintaining relative references involving columns potentially changing order, you can use the RxCy cell reference notation, where x is the row index and y is the column index. To use this, you must specify FALSE (or equivalent, e.g. 0) as the second argument to INDIRECT().

For example, if cell E7 has formula

= E6 + D7

Then you can make all (row and column) references relative using the following formula instead:

= INDIRECT( "R"&(ROW()-1) & "C"&COL(), FALSE ) + INDIRECT( "R"&ROW() & "C"&(COL()-1), FALSE ).

Improve this answer
-1

If you are not using the dollar sign in your formulas sorting should maintain your correct algebra. If you click on a formula cell, you see a small colored box with a pulldown symbol on it. See the image below. I'm referring to the blue box.

enter image description here

If you click on the downward pointing triangle you get the followin pane:

enter image description here

Make sure neither check boxes are checked.

Improve this answer
2
  • Use of $ (or the Numbers equivalent options "Preserve Row/Column") is unrelated to OP's question, since $ only affects how cell references are transformed when copy–pasting a cell
    – Jivan Pal
    Commented Mar 14, 2020 at 20:23
  • It also affects it when sorting. If you have $B$2 then the formula will only look in B2 for a value. If the value gets moved to C5 the formula will still look in B2...
    – Natsfan
    Commented Mar 14, 2020 at 23:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .