software

i have row totals in a range that is H5:H25, but some cells in this
range may have nill value (despite that formula exists). i want to show
the last value in this range in cell H27.
suppose i have a value in H5, H6, and H7 (remaining cells i-e H8:H25
have nill value) i want to show in H27 the last value in that range
which is now in H7.
similarly if the values in range H5:H25 change the cell H7 should
automatically update to the last value appearing in H5:H25.

is there any way?
your expert advice will be appreciated.
i m waiting for quick reply.
thanks.--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=525995Hi!

gt; range may have nill value

Is that a formula blank - quot;quot; ?

Try one of these:

=LOOKUP(9.99999999999999E 307,H5:H25)

=LOOKUP(MAX(H5:H25) 1,H5:H25)

Biff

quot;starguyquot; gt; wrote in
message ...
gt;
gt; i have row totals in a range that is H5:H25, but some cells in this
gt; range may have nill value (despite that formula exists). i want to show
gt; the last value in this range in cell H27.
gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e H8:H25
gt; have nill value) i want to show in H27 the last value in that range
gt; which is now in H7.
gt; similarly if the values in range H5:H25 change the cell H7 should
gt; automatically update to the last value appearing in H5:H25.
gt;
gt; is there any way?
gt; your expert advice will be appreciated.
gt; i m waiting for quick reply.
gt; thanks.
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=525995
gt;

these both formulas work, but these dont ignore cells with nill values.
infact i have sum formula in this range which totals the rows at left
of this range.
it means that cell H5 has formula =sum(B5:G5) and so on.
i want to ignore the nill value and want formula to return the last non
empty cell in this range.

thanks for replying

Biff Wrote:
gt; Hi!
gt;
gt; gt; range may have nill value
gt;
gt; Is that a formula blank - quot;quot; ?
gt;
gt; Try one of these:
gt;
gt; =LOOKUP(9.99999999999999E 307,H5:H25)
gt;
gt; =LOOKUP(MAX(H5:H25) 1,H5:H25)
gt;
gt; Biff
gt;
gt; quot;starguyquot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; i have row totals in a range that is H5:H25, but some cells in this
gt; gt; range may have nill value (despite that formula exists). i want to
gt; show
gt; gt; the last value in this range in cell H27.
gt; gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e H8:H25
gt; gt; have nill value) i want to show in H27 the last value in that range
gt; gt; which is now in H7.
gt; gt; similarly if the values in range H5:H25 change the cell H7 should
gt; gt; automatically update to the last value appearing in H5:H25.
gt; gt;
gt; gt; is there any way?
gt; gt; your expert advice will be appreciated.
gt; gt; i m waiting for quick reply.
gt; gt; thanks.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; starguy
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; starguy's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=525995
gt; gt;--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=525995Try

=INDEX(H5:H25,MATCH(0,H5:H25,0)-1)

If H5:H7 have non-zero values, and H8:H25 are zero, the MATCH function will
return 4, ie the 4th cell in the range is the first cell with zero, the INDEX
function then returns the value in the 3rd cell (i.e. 4 - 1) in its range
argument, which at present is H7.

BrianH

quot;starguyquot; wrote:

gt;
gt; these both formulas work, but these dont ignore cells with nill values.
gt; infact i have sum formula in this range which totals the rows at left
gt; of this range.
gt; it means that cell H5 has formula =sum(B5:G5) and so on.
gt; i want to ignore the nill value and want formula to return the last non
gt; empty cell in this range.
gt;
gt; thanks for replying
gt;
gt; Biff Wrote:
gt; gt; Hi!
gt; gt;
gt; gt; gt; range may have nill value
gt; gt;
gt; gt; Is that a formula blank - quot;quot; ?
gt; gt;
gt; gt; Try one of these:
gt; gt;
gt; gt; =LOOKUP(9.99999999999999E 307,H5:H25)
gt; gt;
gt; gt; =LOOKUP(MAX(H5:H25) 1,H5:H25)
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;starguyquot; gt; wrote
gt; gt; in
gt; gt; message ...
gt; gt; gt;
gt; gt; gt; i have row totals in a range that is H5:H25, but some cells in this
gt; gt; gt; range may have nill value (despite that formula exists). i want to
gt; gt; show
gt; gt; gt; the last value in this range in cell H27.
gt; gt; gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e H8:H25
gt; gt; gt; have nill value) i want to show in H27 the last value in that range
gt; gt; gt; which is now in H7.
gt; gt; gt; similarly if the values in range H5:H25 change the cell H7 should
gt; gt; gt; automatically update to the last value appearing in H5:H25.
gt; gt; gt;
gt; gt; gt; is there any way?
gt; gt; gt; your expert advice will be appreciated.
gt; gt; gt; i m waiting for quick reply.
gt; gt; gt; thanks.
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; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=525995
gt; gt; gt;
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=525995
gt;
gt;


i may have zero value in any cell of this range.
suppose i have 100 in H5, 250 in H6, 175 in H7, sum formula returns
zero in H8, H9 has 200, H10 has againg zero, H11 has 350 and so on.
i want to return the last non zero value in the range. it means if H5
to H7 has values and H8 has zero value then after H8 cells have values
then some cells have zero value and so on. now i want to return the
last non zero value in the range.
i hope that now i could be able to explain my problem properly.
this seems me challenging.
waiting for reply.

BrianH Wrote:
gt; Try
gt;
gt; =INDEX(H5:H25,MATCH(0,H5:H25,0)-1)
gt;
gt; If H5:H7 have non-zero values, and H8:H25 are zero, the MATCH function
gt; will
gt; return 4, ie the 4th cell in the range is the first cell with zero, the
gt; INDEX
gt; function then returns the value in the 3rd cell (i.e. 4 - 1) in its
gt; range
gt; argument, which at present is H7.
gt;
gt; BrianH
gt;
gt; quot;starguyquot; wrote:
gt;
gt; gt;
gt; gt; these both formulas work, but these dont ignore cells with nill
gt; values.
gt; gt; infact i have sum formula in this range which totals the rows at
gt; left
gt; gt; of this range.
gt; gt; it means that cell H5 has formula =sum(B5:G5) and so on.
gt; gt; i want to ignore the nill value and want formula to return the last
gt; non
gt; gt; empty cell in this range.
gt; gt;
gt; gt; thanks for replying
gt; gt;
gt; gt; Biff Wrote:
gt; gt; gt; Hi!
gt; gt; gt;
gt; gt; gt; gt; range may have nill value
gt; gt; gt;
gt; gt; gt; Is that a formula blank - quot;quot; ?
gt; gt; gt;
gt; gt; gt; Try one of these:
gt; gt; gt;
gt; gt; gt; =LOOKUP(9.99999999999999E 307,H5:H25)
gt; gt; gt;
gt; gt; gt; =LOOKUP(MAX(H5:H25) 1,H5:H25)
gt; gt; gt;
gt; gt; gt; Biff
gt; gt; gt;
gt; gt; gt; quot;starguyquot; gt;
gt; wrote
gt; gt; gt; in
gt; gt; gt; message
gt; ...
gt; gt; gt; gt;
gt; gt; gt; gt; i have row totals in a range that is H5:H25, but some cells in
gt; this
gt; gt; gt; gt; range may have nill value (despite that formula exists). i want
gt; to
gt; gt; gt; show
gt; gt; gt; gt; the last value in this range in cell H27.
gt; gt; gt; gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e
gt; H8:H25
gt; gt; gt; gt; have nill value) i want to show in H27 the last value in that
gt; range
gt; gt; gt; gt; which is now in H7.
gt; gt; gt; gt; similarly if the values in range H5:H25 change the cell H7
gt; should
gt; gt; gt; gt; automatically update to the last value appearing in H5:H25.
gt; gt; gt; gt;
gt; gt; gt; gt; is there any way?
gt; gt; gt; gt; your expert advice will be appreciated.
gt; gt; gt; gt; i m waiting for quick reply.
gt; gt; gt; gt; thanks.
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; ------------------------------------------------------------------------
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; www.excelforum.com/showthread...hreadid=525995
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; starguy
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=525995
gt; gt;
gt; gt;--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=525995In J5, enter =IF((H5 J6)gt;0,1,0), and copy down into J5:J25

Then =INDEX(H5:H25,SUM(J5:J25))

Cheers

BrianH

quot;starguyquot; wrote:

gt;
gt; i may have zero value in any cell of this range.
gt; suppose i have 100 in H5, 250 in H6, 175 in H7, sum formula returns
gt; zero in H8, H9 has 200, H10 has againg zero, H11 has 350 and so on.
gt; i want to return the last non zero value in the range. it means if H5
gt; to H7 has values and H8 has zero value then after H8 cells have values
gt; then some cells have zero value and so on. now i want to return the
gt; last non zero value in the range.
gt; i hope that now i could be able to explain my problem properly.
gt; this seems me challenging.
gt; waiting for reply.
gt;
gt; BrianH Wrote:
gt; gt; Try
gt; gt;
gt; gt; =INDEX(H5:H25,MATCH(0,H5:H25,0)-1)
gt; gt;
gt; gt; If H5:H7 have non-zero values, and H8:H25 are zero, the MATCH function
gt; gt; will
gt; gt; return 4, ie the 4th cell in the range is the first cell with zero, the
gt; gt; INDEX
gt; gt; function then returns the value in the 3rd cell (i.e. 4 - 1) in its
gt; gt; range
gt; gt; argument, which at present is H7.
gt; gt;
gt; gt; BrianH
gt; gt;
gt; gt; quot;starguyquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; these both formulas work, but these dont ignore cells with nill
gt; gt; values.
gt; gt; gt; infact i have sum formula in this range which totals the rows at
gt; gt; left
gt; gt; gt; of this range.
gt; gt; gt; it means that cell H5 has formula =sum(B5:G5) and so on.
gt; gt; gt; i want to ignore the nill value and want formula to return the last
gt; gt; non
gt; gt; gt; empty cell in this range.
gt; gt; gt;
gt; gt; gt; thanks for replying
gt; gt; gt;
gt; gt; gt; Biff Wrote:
gt; gt; gt; gt; Hi!
gt; gt; gt; gt;
gt; gt; gt; gt; gt; range may have nill value
gt; gt; gt; gt;
gt; gt; gt; gt; Is that a formula blank - quot;quot; ?
gt; gt; gt; gt;
gt; gt; gt; gt; Try one of these:
gt; gt; gt; gt;
gt; gt; gt; gt; =LOOKUP(9.99999999999999E 307,H5:H25)
gt; gt; gt; gt;
gt; gt; gt; gt; =LOOKUP(MAX(H5:H25) 1,H5:H25)
gt; gt; gt; gt;
gt; gt; gt; gt; Biff
gt; gt; gt; gt;
gt; gt; gt; gt; quot;starguyquot; gt;
gt; gt; wrote
gt; gt; gt; gt; in
gt; gt; gt; gt; message
gt; gt; ...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; i have row totals in a range that is H5:H25, but some cells in
gt; gt; this
gt; gt; gt; gt; gt; range may have nill value (despite that formula exists). i want
gt; gt; to
gt; gt; gt; gt; show
gt; gt; gt; gt; gt; the last value in this range in cell H27.
gt; gt; gt; gt; gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e
gt; gt; H8:H25
gt; gt; gt; gt; gt; have nill value) i want to show in H27 the last value in that
gt; gt; range
gt; gt; gt; gt; gt; which is now in H7.
gt; gt; gt; gt; gt; similarly if the values in range H5:H25 change the cell H7
gt; gt; should
gt; gt; gt; gt; gt; automatically update to the last value appearing in H5:H25.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; is there any way?
gt; gt; gt; gt; gt; your expert advice will be appreciated.
gt; gt; gt; gt; gt; i m waiting for quick reply.
gt; gt; gt; gt; gt; thanks.
gt; gt; gt; gt; 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; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; starguy's Profile:
gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=525995
gt; 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; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=525995
gt; gt; gt;
gt; gt; gt;
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=525995
gt;
gt;

=LOOKUP(2,1/(H5:H25lt;gt;0),H5:H25)

Next time you post a question don't use the term quot;nill valuequot; . Tell us the
cells contain a ZERO. There's no mistaking what ZERO means!

Biff

quot;BrianHquot; gt; wrote in message
...
gt; In J5, enter =IF((H5 J6)gt;0,1,0), and copy down into J5:J25
gt;
gt; Then =INDEX(H5:H25,SUM(J5:J25))
gt;
gt; Cheers
gt;
gt; BrianH
gt;
gt; quot;starguyquot; wrote:
gt;
gt;gt;
gt;gt; i may have zero value in any cell of this range.
gt;gt; suppose i have 100 in H5, 250 in H6, 175 in H7, sum formula returns
gt;gt; zero in H8, H9 has 200, H10 has againg zero, H11 has 350 and so on.
gt;gt; i want to return the last non zero value in the range. it means if H5
gt;gt; to H7 has values and H8 has zero value then after H8 cells have values
gt;gt; then some cells have zero value and so on. now i want to return the
gt;gt; last non zero value in the range.
gt;gt; i hope that now i could be able to explain my problem properly.
gt;gt; this seems me challenging.
gt;gt; waiting for reply.
gt;gt;
gt;gt; BrianH Wrote:
gt;gt; gt; Try
gt;gt; gt;
gt;gt; gt; =INDEX(H5:H25,MATCH(0,H5:H25,0)-1)
gt;gt; gt;
gt;gt; gt; If H5:H7 have non-zero values, and H8:H25 are zero, the MATCH function
gt;gt; gt; will
gt;gt; gt; return 4, ie the 4th cell in the range is the first cell with zero, the
gt;gt; gt; INDEX
gt;gt; gt; function then returns the value in the 3rd cell (i.e. 4 - 1) in its
gt;gt; gt; range
gt;gt; gt; argument, which at present is H7.
gt;gt; gt;
gt;gt; gt; BrianH
gt;gt; gt;
gt;gt; gt; quot;starguyquot; wrote:
gt;gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt; these both formulas work, but these dont ignore cells with nill
gt;gt; gt; values.
gt;gt; gt; gt; infact i have sum formula in this range which totals the rows at
gt;gt; gt; left
gt;gt; gt; gt; of this range.
gt;gt; gt; gt; it means that cell H5 has formula =sum(B5:G5) and so on.
gt;gt; gt; gt; i want to ignore the nill value and want formula to return the last
gt;gt; gt; non
gt;gt; gt; gt; empty cell in this range.
gt;gt; gt; gt;
gt;gt; gt; gt; thanks for replying
gt;gt; gt; gt;
gt;gt; gt; gt; Biff Wrote:
gt;gt; gt; gt; gt; Hi!
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt; range may have nill value
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Is that a formula blank - quot;quot; ?
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Try one of these:
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; =LOOKUP(9.99999999999999E 307,H5:H25)
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; =LOOKUP(MAX(H5:H25) 1,H5:H25)
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Biff
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; quot;starguyquot; gt;
gt;gt; gt; wrote
gt;gt; gt; gt; gt; in
gt;gt; gt; gt; gt; message
gt;gt; gt; ...
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt; i have row totals in a range that is H5:H25, but some cells in
gt;gt; gt; this
gt;gt; gt; gt; gt; gt; range may have nill value (despite that formula exists). i want
gt;gt; gt; to
gt;gt; gt; gt; gt; show
gt;gt; gt; gt; gt; gt; the last value in this range in cell H27.
gt;gt; gt; gt; gt; gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e
gt;gt; gt; H8:H25
gt;gt; gt; gt; gt; gt; have nill value) i want to show in H27 the last value in that
gt;gt; gt; range
gt;gt; gt; gt; gt; gt; which is now in H7.
gt;gt; gt; gt; gt; gt; similarly if the values in range H5:H25 change the cell H7
gt;gt; gt; should
gt;gt; gt; gt; gt; gt; automatically update to the last value appearing in H5:H25.
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt; is there any way?
gt;gt; gt; gt; gt; gt; your expert advice will be appreciated.
gt;gt; gt; gt; gt; gt; i m waiting for quick reply.
gt;gt; gt; gt; gt; gt; thanks.
gt;gt; gt; gt; gt; gt;
gt;gt; 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; gt; gt; gt;
gt;gt; gt; ------------------------------------------------------------------------
gt;gt; gt; gt; gt; gt; starguy's Profile:
gt;gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=32434
gt;gt; gt; gt; gt; gt; View this thread:
gt;gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=525995
gt;gt; gt; gt; gt; gt;
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;gt; gt; gt; starguy's Profile:
gt;gt; gt; www.excelforum.com/member.php...oamp;userid=32434
gt;gt; gt; gt; View this thread:
gt;gt; gt; www.excelforum.com/showthread...hreadid=525995
gt;gt; gt; gt;
gt;gt; gt; gt;
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=525995
gt;gt;
gt;gt;

thanks you both BrianH and Biff
both ways work well
sorry Biff for using wrong term.

thanks again--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=525995You're welcome.

Biff

quot;starguyquot; gt; wrote in
message ...
gt;
gt; thanks you both BrianH and Biff
gt; both ways work well
gt; sorry Biff for using wrong term.
gt;
gt; thanks again
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=525995
gt;
Thanks Biff, good one

More elegant than mine, and works with text or blanks in the column as well!

BrianH

quot;Biffquot; wrote:

gt; =LOOKUP(2,1/(H5:H25lt;gt;0),H5:H25)
gt;
gt; Next time you post a question don't use the term quot;nill valuequot; . Tell us the
gt; cells contain a ZERO. There's no mistaking what ZERO means!
gt;
gt; Biff
gt;
gt; quot;BrianHquot; gt; wrote in message
gt; ...
gt; gt; In J5, enter =IF((H5 J6)gt;0,1,0), and copy down into J5:J25
gt; gt;
gt; gt; Then =INDEX(H5:H25,SUM(J5:J25))
gt; gt;
gt; gt; Cheers
gt; gt;
gt; gt; BrianH
gt; gt;
gt; gt; quot;starguyquot; wrote:
gt; gt;
gt; gt;gt;
gt; gt;gt; i may have zero value in any cell of this range.
gt; gt;gt; suppose i have 100 in H5, 250 in H6, 175 in H7, sum formula returns
gt; gt;gt; zero in H8, H9 has 200, H10 has againg zero, H11 has 350 and so on.
gt; gt;gt; i want to return the last non zero value in the range. it means if H5
gt; gt;gt; to H7 has values and H8 has zero value then after H8 cells have values
gt; gt;gt; then some cells have zero value and so on. now i want to return the
gt; gt;gt; last non zero value in the range.
gt; gt;gt; i hope that now i could be able to explain my problem properly.
gt; gt;gt; this seems me challenging.
gt; gt;gt; waiting for reply.
gt; gt;gt;
gt; gt;gt; BrianH Wrote:
gt; gt;gt; gt; Try
gt; gt;gt; gt;
gt; gt;gt; gt; =INDEX(H5:H25,MATCH(0,H5:H25,0)-1)
gt; gt;gt; gt;
gt; gt;gt; gt; If H5:H7 have non-zero values, and H8:H25 are zero, the MATCH function
gt; gt;gt; gt; will
gt; gt;gt; gt; return 4, ie the 4th cell in the range is the first cell with zero, the
gt; gt;gt; gt; INDEX
gt; gt;gt; gt; function then returns the value in the 3rd cell (i.e. 4 - 1) in its
gt; gt;gt; gt; range
gt; gt;gt; gt; argument, which at present is H7.
gt; gt;gt; gt;
gt; gt;gt; gt; BrianH
gt; gt;gt; gt;
gt; gt;gt; gt; quot;starguyquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; these both formulas work, but these dont ignore cells with nill
gt; gt;gt; gt; values.
gt; gt;gt; gt; gt; infact i have sum formula in this range which totals the rows at
gt; gt;gt; gt; left
gt; gt;gt; gt; gt; of this range.
gt; gt;gt; gt; gt; it means that cell H5 has formula =sum(B5:G5) and so on.
gt; gt;gt; gt; gt; i want to ignore the nill value and want formula to return the last
gt; gt;gt; gt; non
gt; gt;gt; gt; gt; empty cell in this range.
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; thanks for replying
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; Biff Wrote:
gt; gt;gt; gt; gt; gt; Hi!
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; gt; range may have nill value
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; Is that a formula blank - quot;quot; ?
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; Try one of these:
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; =LOOKUP(9.99999999999999E 307,H5:H25)
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; =LOOKUP(MAX(H5:H25) 1,H5:H25)
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; Biff
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; quot;starguyquot; gt;
gt; gt;gt; gt; wrote
gt; gt;gt; gt; gt; gt; in
gt; gt;gt; gt; gt; gt; message
gt; gt;gt; gt; ...
gt; gt;gt; gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; gt; i have row totals in a range that is H5:H25, but some cells in
gt; gt;gt; gt; this
gt; gt;gt; gt; gt; gt; gt; range may have nill value (despite that formula exists). i want
gt; gt;gt; gt; to
gt; gt;gt; gt; gt; gt; show
gt; gt;gt; gt; gt; gt; gt; the last value in this range in cell H27.
gt; gt;gt; gt; gt; gt; gt; suppose i have a value in H5, H6, and H7 (remaining cells i-e
gt; gt;gt; gt; H8:H25
gt; gt;gt; gt; gt; gt; gt; have nill value) i want to show in H27 the last value in that
gt; gt;gt; gt; range
gt; gt;gt; gt; gt; gt; gt; which is now in H7.
gt; gt;gt; gt; gt; gt; gt; similarly if the values in range H5:H25 change the cell H7
gt; gt;gt; gt; should
gt; gt;gt; gt; gt; gt; gt; automatically update to the last value appearing in H5:H25.
gt; gt;gt; gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; gt; is there any way?
gt; gt;gt; gt; gt; gt; gt; your expert advice will be appreciated.
gt; gt;gt; gt; gt; gt; gt; i m waiting for quick reply.
gt; gt;gt; gt; gt; gt; gt; thanks.
gt; gt;gt; gt; gt; gt; gt;
gt; gt;gt; gt; gt; 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; gt;gt; gt; gt; gt;
gt; gt;gt; gt; ------------------------------------------------------------------------
gt; gt;gt; gt; gt; gt; gt; starguy's Profile:
gt; gt;gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt;gt; gt; gt; gt; gt; View this thread:
gt; gt;gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=525995
gt; gt;gt; gt; gt; gt; gt;
gt; gt;gt; gt; 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; 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; gt; View this thread:
gt; gt;gt; gt; www.excelforum.com/showthread...hreadid=525995
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt;
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=525995
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;