how to extract last value of a column

Beleer · January 1, 2020, 10:21am

I need to use the last (lowest) value of a column of figures in my speadsheet

mikekaganski · January 1, 2020, 10:53am

You may use e.g. this array formula - for column A:

=INDEX(A:A;MAX(ROW(A:A)*(A:A<>"")))

Lupp · January 2, 2020, 12:27am

If such a lookup is urgently needed for many and/or large ranges, a solution based on user code (drafting on .queryEmptyCells and/or .queryContentCells might be preferrable.

Beleer · January 2, 2020, 9:23am

Thanks Mike,
Since I’m no expert in spreasheets, I understand the array formula as:
*A:A meaning “column A”
MAX(ROW(A:A) meaning the highest row value in column A (bottommost cell in column A)
*(A:A<>" ") meaning “until you come to en empty cell”
This sounds good, but when I enter it in a cell I get #NOM? (this is French for I suppose #NAME?)

MarkMcLean · January 1, 2020, 5:11pm

If I understand your question =MIN(A:A). It will look from top to bottom in column “A”, so put it in another column, not in “A”. If you desire it in “A”, do such at bottom of #'s, like this in A51, MIN(A5:A50)

Lupp · January 2, 2020, 12:19am

I would suppose the alternative word “lowest” used in the question’s body as an alternative for “last” was meant to mean “bottommost” (not “smallest” or “minimal”) by the original questioner.
This is what also @mikekaganski assumed when he answered.

Beleer · January 2, 2020, 9:35am

Thanks Mark !
The MIN formula will give me the lowest value which is not what I am looking for. You are right assuming that I need the latest (bottommost) entry in the column. But Mike’s formula doesn’t work when I use it: it shows #NOM?