excel function to find last number in column

Question

I record the value of my stocks each day in columns and it is a long spreadsheet. In the top cell of each column, I want to use a function that will display the last entry in the column automatically. I've tried the Index function and the Index function combined with the Counta function with no success. Any suggestions?

Answer 1

Try using LOOKUP, assuming at most 1000 rows of data (adjust as required) use this formula in A1 to get the last number in A2:A1000

=LOOKUP(9.99E+307,A2:A1000)

You can have blanks or any other data in the range (even errors) and it will still return the last number

Answer 2

Try this for Column A, resp. Cell A1:

=OFFSET(A$2;ROWS(A$2:A$101)-COUNTBLANK(A$2:A$101)-1;0)

This example is for a maximum of 100 rows of data (2 - 101). You may replace 101 by any higher number according to the size of your sheet.

Please note that there may be no blank cells in the middle of the list.