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?


--
Fenneth
------------------------------------------------------------------------
Fenneth's Profile: http://www.excelforum.com/member.php...o&userid=33655
View this thread: http://www.excelforum.com/showthread...hreadid=534500

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fenneth" wrote in
message ...

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?


--
Fenneth
------------------------------------------------------------------------
Fenneth's Profile:
http://www.excelforum.com/member.php...o&userid=33655
View this thread: http://www.excelforum.com/showthread...hreadid=534500

try this?

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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=534500

Hi,

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


Igbert


"starguy" wrote:


try this?

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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=534500

Hi!

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER:

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

Biff

"igbert" wrote in message
...
Hi,

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


Igbert


"starguy" wrote:


try this?

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=534500

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" wrote:

Hi!

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER:

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

Biff

"igbert" wrote in message
...
Hi,

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


Igbert


"starguy" wrote:


try this?

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=534500

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

"igbert" wrote in message
...
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" wrote:

Hi!

Try this entered as an array using the key combination of
CTRL,SHIFT,ENTER:

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

Biff

"igbert" wrote in message
...
Hi,

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


Igbert


"starguy" wrote:


try this?

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=534500

Hi Bob,

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


Igbert


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fenneth" wrote in
message ...

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?


--
Fenneth
------------------------------------------------------------------------
Fenneth's Profile:
http://www.excelforum.com/member.php...o&userid=33655
View this thread: http://www.excelforum.com/showthread...hreadid=534500