Lowest entry in a column

A

Astley

Hi everyone,

Can anyone tell me how to automatically use the last/lowest entry in a
column? I don't want to sort the cells, or choose the Maximum or Minimum - I
just need to use the bottom entry in a column automatically in a formula
I'll create somewhere else on the spreadsheet.
It thought it would be in the functions list somewhere, but it has eluded
me!

Thanks,
Astley
 
M

Mangesh Yadav

Suppose A is the column in question, use the following formula to refer to
the last cell:
=INDIRECT("A"&COUNT(A:A))


Mangesh



....
 
D

Dave Peterson

Mangesh's response will work if column A contains all numbers. But if you have
text or gaps in the column, then it will not give the correct result.

If you have no gaps, but do have text (or text and numbers):

=indirect("A"&counta(a:a))

If you have anything (gaps, numbers, text), then this will work ok:

=LOOKUP(2,1/(A1:A9999<>""),A1:A9999)

Change 9999 (both spots) to be as large as you need, but don't use the entire
column.
 
Top