Select last value

  • Thread starter Thread starter Carl
  • Start date Start date
C

Carl

I am trying to select the last (bottom) value on a one-column list. I am
using the COUNT function to designate the bottom value that is not zero, and
the CHOOSE function to select the designated value. But, I can't make that
work. Help appreciated.
 
=INDEX(B1:B1000,MAX(IF(B1:B1000<>"",ROW(B1:B1000))))

which is an array formula, so commit with Ctrl-Shift-Enter
 
To write a formula that will always give you the contents of a cell that is the bottom cell in a list that is growing, try using the
formula “=INDEX(A:A,COUNT(A:A))” where “A:A” represents the column where the list is. The index function pulls the value from an
intersection of a column and a row, and the count function determines which row to use by counting the number of items currently in
the list. If you have other things in that column then you would simply choose a range that will always be big enough to hold the
list.

This does not need to be an array formula. Give it a shot.

HTH
--
RMC,CPA


I am trying to select the last (bottom) value on a one-column list. I am
using the COUNT function to designate the bottom value that is not zero, and
the CHOOSE function to select the designated value. But, I can't make that
work. Help appreciated.
 
R. Choate said:
To write a formula that will always give you the contents of a cell that
is the bottom cell in a list that is growing, try using the
formula "=INDEX(A:A,COUNT(A:A))" where "A:A" represents the column where
the list is. The index function pulls the value from an
intersection of a column and a row, and the count function determines
which row to use by counting the number of items currently in
the list. If you have other things in that column then you would simply
choose a range that will always be big enough to hold the
list.

This does not need to be an array formula. Give it a shot.

HTH
Click to expand...
R. Choate,
Works very well.
Thanks!
Carl
 
Back
Top