Finding the bottom non-blank cell in a range

[Fenneth]

Forgive me for these trivial questions, but I cant find the answer using
the documentation.

I want a formula to use the bottom non-blank cell in a range. How can
this be achieved?

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.LastValue.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[starguy]

try this?

=LOOKUP(2,1/(range<>""),range)

 

[Guest]

Hi,

Ias there a similar formula to find the first non blank cell in a range?


Igbert

 

[Guest]

Thanks for the great formula. It works for both text and numbers.

I am new with Index and Match functions. Please kindly explain the logic of
this formula.

Many thanks.


Igbert

 

[Biff]

Please kindly explain the logic of this formula.

Sure!

I'll use a smaller range to demonstrate.

Assume the range of cells is A1:A5:

A1 = (empty)
A2 = (empty)
A3 = (empty)
A4 = XX
A5 = YY

=INDEX(A1:A5,MATCH(TRUE,A1:A5<>"",0))

The Index function holds an array of values. In this case those values are
from the range A1:A5.

Each of these values is in a relative position within the array.

A1 = position 1
A2 = position 2
A3 = position 3
A4 = position 4
A5 = position 5

Using the formula, we want to find the first non-empty cell in that array so
we can use the Match function to tell the Index function which value to
return.

MATCH(TRUE,A1:A5<>"",0)

This expression will return an array of TRUEs and FALSEs:

A1:A5<>""

A1 <>"" = FALSE
A2 <>"" = FALSE
A3 <>"" = FALSE
A4 <>"" = TRUE
A5 <>"" = TRUE

This is what it looks like inside the Match function:

MATCH(TRUE,{FALSE;FALSE;FALSE;TRUE;TRUE},0)

MATCH returns the number of the relative position of the first instance of
the lookup_value.

The lookup_value is TRUE and has been found in the 4th position within the
array:

{FALSE;FALSE;FALSE;TRUE;TRUE}

So, now the formula looks like this:

=INDEX(A1:A5,4)

This returns the value from the 4th position of the indexed array:

A1 = position 1
A2 = position 2
A3 = position 3
A4 = position 4
A5 = position 5

So, the formula returns the value from A4:

A1 = (empty)
A2 = (empty)
A3 = (empty)
A4 = XX
A5 = YY

=INDEX(A1:A5,MATCH(TRUE,A1:A5<>"",0)) = XX

Biff

 

[Guest]

Hi Bob,

Many thanks for the thorough explantion. It is very clear.


Igbert

See http://www.xldynamic.com/source/xld.LastValue.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)