macosapple-numbers

How do I reference the last filled in cell in of a column in Apple's Numbers app (OS X)

I need the footer of a table in Numbers to calculate the difference between the first value in a column and the last value in a column. The last value may be greater, or smaller than the first. How do I reference the last filled in value of a column?

Solution

  • Rather than counting forwards from the first cell as Alexandre's answer does, OFFSET can count backwards from the current cell. I have found this more reliable.

    So for example, if your total/difference is in cell A23, and the last cell is immediately above it, you could reference it using:

    =OFFSET(A23, -1, 0)

    This will continue to work correctly when rows above are inserted or deleted - it will always reference the cell immediately above.