How to reference the last filled cell in a column in Apple Numbers app (OS X)
I need a table footer in Numbers
order to calculate the difference between the first value in a column and the last value in a column. The last value can be greater or less than the first. How do I refer to the last populated column value?
Instead of counting hops from the first cell, as Alexander's answer does, OFFSET can count backward from the current cell. I found this to be more reliable.
For example, if your sum / difference is in cell A23, and the last cell is just above it, you can reference it using:
= OFFSET (A23, -1, 0)
This will continue to work correctly when rows above are inserted or deleted - this will always refer to the cell immediately above.
If you have no blank cells between the first and last record, the last record can be found using COUNT and retrieved using OFFSET. Example:
= OFFSET (A $ 1, COUNT (A) -1, 0)
then the difference between the first and the last will be something like this:
= OFFSET (A $ 1, COUNT (A) -1, 0) - A $ 1