Last Value in a Range

[sgl]

I have the following formula results in a range B7:H7

B C D E F G H
10 25 0 12 0 13 17

I need a formula that will extract the last value in the range that is "17".
These values are claculated from embeded formulas in each cell so even though
columns D and F have a zero value they still have a formula in them

Many thanks/sgl

 

[Mike H]

Try

=LOOKUP(6.022*10^23,B7:H7)

Mike

 

[Mike H]

Maybe your last value could be zero and you want the last number that isn't
zero si try this instead

=LOOKUP(2,1/(B7:H7<>0),B7:H7)

Mike

 

[Gary''s Student]

Please explain...The last value in the range from B7 thru H7 will always be
the contents of H7??

 

[sgl]

Mike, Thanks a lot for te very fast reply. Works great.

sgl

 

[sgl]

Sory my mistake for not being more explicit. The columns do not end at H7 but
at M7. From I7 to M7 they are zero values at present but may be calculated at
any time. My apologies once again for not being more explicit.

Mike H reply above was the solution I was looking for.

sgl

 

[T. Valko]

For the rightmost number excluding zeros:

=LOOKUP(1E100,1/B7:M7,B7:M7)

Assumes there are no logical values (TRUE) in the range.

 

[Harlan Grove]

For the rightmost number excluding zeros:

=LOOKUP(1E100,1/B7:M7,B7:M7)

Assumes there are no logical values (TRUE) in the range.

....

Why assume? Bypass 'em.

=LOOKUP(1E+300,B7:M7/(B7:M7<>0)/(B7:M7<>TRUE))

 

[Bernd P]

Hello,

Why only bypassing TRUE logical values?

=LOOKUP(MAXNUM,B7:M7/(ISNUMBER(B7:M7)*NOT(ISBLANK(B7:M7))))

Set MAXNUM to 1e307 or something you can live with. If you really need
0 instead of blank cells (which I would prefer because a result COULD
be 0) to represent non-filled cells, substitute NOT(ISBLANK(B7:M7)) by
(B7:M7<>0).

Regards,
Bernd