Clever Geek Handbook

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?

+3


source to share


2 answers


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.

+1


source


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

+6


source






More articles:


All Articles