How to add a row without changing formula in Numbers app on Mac

Question

I have rows of data sorted by date (descending). Therefore it is important that a new row can be added above all other rows but before the "header" row.

However, when I select "Add row above" the formulas are updated. I would like the cells referenced in the formulas to not be updated. I know this is possible in Excel but is this possible in Numbers? Any help would be much appreciated.

Answer 1

You can accomplish this using INDIRECT and OFFSET functions.

To give you an example, let's assume that you have just one column of five data points, with the first row being the header and second row onward being the data. Now you want to sum the column up.

  || A
==||======
1 || Sale
2 ||  1
3 ||  2
4 ||  3
5 ||  4
6 ||  5

and you want SUM from the first value to the last value always. For a fixed set like the one shown above, you could just do =SUM(A2:A6) in the A7 cell.

But you want to be able to add rows to the top. And you expect the function to be able to sum from the first data row to the last data row just before where the formula is used. So you would use INDIRECT("A2") as the starting index (A2 doesn't change even if you add a new row), and OFFSET(A7, -1, 0, 1, 1) as the last index (A7 will change to A8 if a new row is added, and the offset will make sure that you're still summing until one cell above A8).

=SUM(INDIRECT("A2"):OFFSET(A7,-1,0,1,1))

Now, even if you add a new row at the top, your formula will work just fine.

I hope this helps.

Answer 2

While you tagged it mac, I haven't tried a spreadsheet on a mac, but you've also tagged your question microsoft excel so i'll answer for microsoft excel. Try sticking a dollar sign before any letter or number that you want to remain constant e.g. to make a or 1 constant for a1, try $a1 or $a$1 or a$1