Last Value in range (X2K)

Topic

New Reply

I am looking for a formula that will give me the last value in a column of figures. I am sure this has been posted before but I could not find it in search

Many Thanks

Peter

Reply | Quote

Replies

For instance, to find the value of the last used cell in column A, use this formula:

=OFFSET(A2,MATCH(9.99999999999999E+307,A2:A65536),0)

I found this formula by Rory in post 168137 by searching for “last cell column”, search option “And”.

Reply | Quote

Thanks Hans

Reply | Quote

How about the last numerical (as opposed to text) value in a row?

0

Reply | Quote

Try something like:
=OFFSET(A2,0,MATCH(9.99999999999999E+307,2:2)-1)
if your formula is in A1. You will need to adjust the cell and row numbers according to where your formula is and which row you’re looking in. (It’s basically the same formula as for columns, but swapping the 0 and the MATCH expression round in the OFFSET function)
HTH.

Reply | Quote

=LOOKUP(9.99999999999999E+307,2:2)

or

=INDEX(2:2,MATCH(9.99999999999999E+307,2:2)

will return the last numeric value (a number, a date, or a time value).

The formula is also applicable to definite ranges…

=LOOKUP(9.99999999999999E+307,A2:M2)

Reply | Quote

Thanks, Aladin-both work just fine!

Reply | Quote

You don’t need volatile OFFSET…

Either

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

or

=LOOKUP(9.99999999999999E+307,A:A)

Reply | Quote