Getting Last Numeric Value In Column

cloud forest kid wrote:
I used to use excel and have switched to Numbers but now I'm kinda' stuck. How can I retrieve the last numeric value in a defined column. In Excel I would do it as:

=VLOOKUP(9.9999999999E+307,K,1)

With excel that gives me the last value in a column. In Numbers this formula renders the highest value of that column.

Use the INDEX formula which looks for an intersection of row and columns You have to say "within this range, look for the crossroads where X happens." It looks for range of cells, row number, column number, and area index. In the example below, it is looking at:

- The range of column D because I only want the highest number in column D

- The row based on COUNTA which is counting cells that have values

- The column 1 because I've only identified a single column (If my range was C:D, I would put 2 to look at D)

- Area index is optional and blank in this case

=INDEX(D2:D21,(COUNTA(D2:D21)),1,area-index)

Hi Matt,

Your formula shares the same weakness as Jerry noted in his initial offering on April 9, 2011.

COUNT(D), used in Jerry's formula, counts only the cells holding numerical values, so the row-offset it generates will be one too small for every cell above the last entry in column D that is empty or that contains a non-numerical value.

COUNTA(D), used in your formula, counts only the cells containing a value, so the row-index it generates will be one too small for every cell above the last entry in column D that is empty.

Note: These two statements from your post are correct, but not complete:

- The column 1 because I've only identified a single column (If my range was C:D, I would put 2 to look at D)

- Area index is optional and blank in this case

-column-index is needed only when range contains more than one column.

-area-index is needed only when range contains more than one collection of cells.

See the description and usage notes in the Function Browser for details.

The formula shown above is equivalent to yours, but eliminates the unnecessary column and area index arguments.

Badunit's use of an index column and a lookup function solves the issue of empty cells or cells containing non-numeric data in the target column by indexing only the rows containing a number, then returning the value from the row with the greatest index number.

Regards,

Barry

cloud forest kid wrote:
Maybe I did not ask the question correctly. Let me try again.

Let's say you have a long column of numbers:
8
2
3
6
9
1
4
3
2
9
2

I'm looking for a formula that will grab the last number in the column. In this case it would be the value "2". I'm not looking for it to give me the highest number in that column, I'm looking for the last number value in the column.

Jerry, really nice formula here!!

This formula also works outside of the header, allowing a user to link back to a separate column to retrieve the results.

+1 for Jerr 🙂