software

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: www.excelforum.com/member.php...oamp;userid=33655
View this thread: www.excelforum.com/showthread...hreadid=534500See www.xldynamic.com/source/xld.LastValue.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Fennethquot; gt; wrote in
message ...
gt;
gt; Forgive me for these trivial questions, but I cant find the answer using
gt; the documentation.
gt;
gt; I want a formula to use the bottom non-blank cell in a range. How can
gt; this be achieved?
gt;
gt;
gt; --
gt; Fenneth
gt; ------------------------------------------------------------------------
gt; Fenneth's Profile:
www.excelforum.com/member.php...oamp;userid=33655
gt; View this thread: www.excelforum.com/showthread...hreadid=534500
gt;

try this?

=LOOKUP(2,1/(rangelt;gt;quot;quot;),range)--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=534500Hi,

Ias there a similar formula to find the first non blank cell in a range?Igbertquot;starguyquot; wrote:

gt;
gt; try this?
gt;
gt; =LOOKUP(2,1/(rangelt;gt;quot;quot;),range)
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=534500
gt;
gt;

Hi!

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

=INDEX(A1:A100,MATCH(TRUE,A1:A100lt;gt;quot;quot;,0))

Biff

quot;igbertquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Ias there a similar formula to find the first non blank cell in a range?
gt;
gt;
gt; Igbert
gt;
gt;
gt; quot;starguyquot; wrote:
gt;
gt;gt;
gt;gt; try this?
gt;gt;
gt;gt; =LOOKUP(2,1/(rangelt;gt;quot;quot;),range)
gt;gt;
gt;gt;
gt;gt; --
gt;gt; starguy
gt;gt; ------------------------------------------------------------------------
gt;gt; starguy's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=32434
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=534500
gt;gt;
gt;gt;
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.Igbertquot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt;
gt; =INDEX(A1:A100,MATCH(TRUE,A1:A100lt;gt;quot;quot;,0))
gt;
gt; Biff
gt;
gt; quot;igbertquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; Ias there a similar formula to find the first non blank cell in a range?
gt; gt;
gt; gt;
gt; gt; Igbert
gt; gt;
gt; gt;
gt; gt; quot;starguyquot; wrote:
gt; gt;
gt; gt;gt;
gt; gt;gt; try this?
gt; gt;gt;
gt; gt;gt; =LOOKUP(2,1/(rangelt;gt;quot;quot;),range)
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; starguy
gt; gt;gt; ------------------------------------------------------------------------
gt; gt;gt; starguy's Profile:
gt; gt;gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt;gt; View this thread:
gt; gt;gt; www.excelforum.com/showthread...hreadid=534500
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

gt;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:A5lt;gt;quot;quot;,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:A5lt;gt;quot;quot;,0)

This expression will return an array of TRUEs and FALSEs:

A1:A5lt;gt;quot;quot;

A1 lt;gt;quot;quot; = FALSE
A2 lt;gt;quot;quot; = FALSE
A3 lt;gt;quot;quot; = FALSE
A4 lt;gt;quot;quot; = TRUE
A5 lt;gt;quot;quot; = 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:A5lt;gt;quot;quot;,0)) = XX

Biff

quot;igbertquot; gt; wrote in message
...
gt; Thanks for the great formula. It works for both text and numbers.
gt;
gt; I am new with Index and Match functions. Please kindly explain the logic
gt; of
gt; this formula.
gt;
gt; Many thanks.
gt;
gt;
gt; Igbert
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this entered as an array using the key combination of
gt;gt; CTRL,SHIFT,ENTER:
gt;gt;
gt;gt; =INDEX(A1:A100,MATCH(TRUE,A1:A100lt;gt;quot;quot;,0))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;igbertquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi,
gt;gt; gt;
gt;gt; gt; Ias there a similar formula to find the first non blank cell in a
gt;gt; gt; range?
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; Igbert
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;starguyquot; wrote:
gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt; try this?
gt;gt; gt;gt;
gt;gt; gt;gt; =LOOKUP(2,1/(rangelt;gt;quot;quot;),range)
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; starguy
gt;gt; gt;gt; ------------------------------------------------------------------------
gt;gt; gt;gt; starguy's Profile:
gt;gt; gt;gt; www.excelforum.com/member.php...oamp;userid=32434
gt;gt; gt;gt; View this thread:
gt;gt; gt;gt; www.excelforum.com/showthread...hreadid=534500
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Hi Bob,

Many thanks for the thorough explantion. It is very clear. Igbertquot;Bob Phillipsquot; wrote:

gt; See www.xldynamic.com/source/xld.LastValue.html
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Fennethquot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; Forgive me for these trivial questions, but I cant find the answer using
gt; gt; the documentation.
gt; gt;
gt; gt; I want a formula to use the bottom non-blank cell in a range. How can
gt; gt; this be achieved?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Fenneth
gt; gt; ------------------------------------------------------------------------
gt; gt; Fenneth's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33655
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=534500
gt; gt;
gt;
gt;
gt;