Last Value in a Range

  • Thread starter Thread starter sgl
  • Start date Start date
S

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
 
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
 
Please explain...The last value in the range from B7 thru H7 will always be
the contents of H7??
 
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
 
For the rightmost number excluding zeros:

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

Assumes there are no logical values (TRUE) in the range.
 
T. Valko said:
For the rightmost number excluding zeros:

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

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

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
 
Back
Top