Showing Last value in a cell

[starguy]

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.

 

[Biff]

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]

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

 

[Guest]

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]

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.

 

[Guest]

In J5, enter =IF((H5+J6)>0,1,0), and copy down into J5:J25

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

Cheers

BrianH

 

[Biff]

=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

 

[starguy]

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

thanks again

 

[Biff]

You're welcome.

Biff

 

[Guest]

Thanks Biff, good one

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

BrianH