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" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[BrianH]

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" <[email protected]> wrote
> > in
> > message news:[email protected]...
> > >
> > > 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]

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. (keeping in mind that H5:H25 range has formula =sum(B5:G5) and filled down to H25)
i hope that now i could be able to explain my problem properly.
this seems me challenging.
waiting for reply.

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" <[email protected]> wrote
> > in
> > message news:[email protected]...
> > >
> > > 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
>
>

[BrianH]

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" <[email protected]>
> > wrote
> > > > in
> > > > message
> > news:[email protected]...
> > > > >
> > > > > 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
>
>

[BrianH]

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" <[email protected]>
> > wrote
> > > > in
> > > > message
> > news:[email protected]...
> > > > >
> > > > > 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
>
>

[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

"BrianH" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
>> > wrote
>> > > > in
>> > > > message
>> > news:[email protected]...
>> > > > >
>> > > > > 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
>>
>>

[starguy]

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

thanks again

[Biff]

You're welcome.

Biff

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> thanks you both BrianH and Biff
> both ways work well
> sorry Biff for using wrong term.
>
> thanks again
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=525995
>

[BrianH]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]>
> >> > wrote
> >> > > > in
> >> > > > message
> >> > news:[email protected]...
> >> > > > >
> >> > > > > 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
> >>
> >>
>
>
>