Thanks Biff, good one

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

BrianH

"Biff" wrote:

=LOOKUP(2,1/(H5:H25<0),H5:H25)

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

Biff

"BrianH" wrote in message
...
In J5, enter =IF((H5+J6)0,1,0), and copy down into J5:J25

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

Cheers

BrianH

"starguy" wrote:


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:
Try

=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

"starguy" wrote:


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:
Hi!

range may have nill value

Is that a formula blank - "" ?

Try one of these:

=LOOKUP(9.99999999999999E+307,H5:H25)

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

Biff

"starguy"
wrote
in
message
...

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:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=525995



--
starguy

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




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