Last value in column...

  • Thread starter Thread starter Django
  • Start date Start date
D

Django

Is there any way to find tha last value in column. I have several columns
containing values and also empty cells. I'd like to find the last (lowest)
value in every column with a formula.

Thanks!
 
Try this, will return last "value" in column A that is equal or less than
whatever 99^99 equates to (increase that value if needed). If the last cell
in column A is text, it will return the last numeric value in A.

=LOOKUP(99^99,A:A)

HTH
Regards,
Howard
 
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

Will return the last value in column A......ignores blank cells.


Gord Dibben MS Excel MVP
 
How about setting the formula to return next to the last value or nth value from the last value? Thanks



Gord Dibben wrote:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)Will return the last value in column A...
16-Oct-09

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

Will return the last value in column A......ignores blank cells

Gord Dibben MS Excel MV

wrote:

Previous Posts In This Thread:

Last value in column...
Is there any way to find tha last value in column. I have several column
containing values and also empty cells. I'd like to find the last (lowest
value in every column with a formula

Thanks!

Try this, will return last "value" in column A that is equal or less
Try this, will return last "value" in column A that is equal or less tha
whatever 99^99 equates to (increase that value if needed). If the last cel
in column A is text, it will return the last numeric value in A

=LOOKUP(99^99,A:A

HT
Regards
Howard

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)Will return the last value in column A...
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535

Will return the last value in column A......ignores blank cells

Gord Dibben MS Excel MV

wrote:

EggHeadCafe - Software Developer Portal of Choice
Dr. Dotnetsky's Cool .NET Tips & Tricks # 12
http://www.eggheadcafe.com/tutorial...8968-ab20ab99320d/dr-dotnetskys-cool-net.aspx
 
Assuming there are no empty/blank cells within the range...

=INDEX(A:A,COUNTA(A:A)-n+1)

Where n = the nth last item you want.

For the last item n = 1
 
Try these *array* formulas, which work for text and/or numbers, with the nth
value entered in C1:

=INDEX(A1:A20,LARGE(ROW(A1:A20)*(A1:A20<>0),C1))
=INDEX(A1:A20,LARGE(ROW(A1:A20)*(A1:A20<>""),C1))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

Don't know if you have empty cells or zeroes.
 
Back
Top