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

Try

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

Mike

"sgl" wrote:

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

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

"Mike H" wrote:

Try

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

Mike

"sgl" wrote:

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

Please explain...The last value in the range from B7 thru H7 will always be
the contents of H7??
--
Gary''s Student - gsnu200838


"sgl" wrote:

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, Thanks a lot for te very fast reply. Works great.

sgl

"Mike H" wrote:

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

"Mike H" wrote:

Try

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

Mike

"sgl" wrote:

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

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

"Gary''s Student" wrote:

Please explain...The last value in the range from B7 thru H7 will always be
the contents of H7??
--
Gary''s Student - gsnu200838


"sgl" wrote:

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

For the rightmost number excluding zeros:

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

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

--
Biff
Microsoft Excel MVP


"sgl" wrote in message
...
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

"Gary''s Student" wrote:

Please explain...The last value in the range from B7 thru H7 will always
be
the contents of H7??
--
Gary''s Student - gsnu200838


"sgl" wrote:

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

"T. Valko" wrote...
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))

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