5

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?

Community
  • 1
  • 1
Dave Sag
  • 13,266
  • 14
  • 86
  • 134

3 Answers3

7

If you don't have empty cells between the first and last entry, the last entry can be found using COUNT, and retrieved using OFFSET. Example :

=OFFSET(A$1, COUNT(A)-1, 0)

the difference between the first and last would then be something like:

=OFFSET(A$1, COUNT(A)-1, 0) - A$1

Alexandre Dion
  • 9,130
  • 2
  • 41
  • 29
  • 2
    actually it's `=OFFSET(A$1,COUNT(A)-1,0)-A$1` as `OFFSET` indices start at 0 but yes thanks that's what I was looking for. – Dave Sag Mar 23 '12 at 04:38
3

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.

Ben23
  • 159
  • 1
  • 5
-2

In my case I needed to get the last row result but with a date field; I just tested with MAX function and as dates goes on (month after month, etc), this function got what I needed. So far using with about 20 rows added (no blank rows in the reference column) and it is working fine. Hope to have added another option.

= MAX(A)

P3d70
  • 1
  • 1
  • `MAX` returns the largest number in a collection. I'm not sure how that's relevant here. – Galen Mar 26 '23 at 06:59