What formula would give a value of a last negative value in a range?

  • Thread starter Thread starter Dmitriy Kopnichev
  • Start date Start date
D

Dmitriy Kopnichev

Hello
What formula would give a value of a last negative value in a range? I want
to calculate a pay-back period from a cumulative cash-flow or a cash-flow.
 
One way

=INDEX(A1:A40,MAX((A1:A40<0)*(ROW(A1:A40))))

entered with ctrl + shift & enter

note that since ROW starts from the first row you have to offset it so if
you range is in A10:A50 instead you
have to use either

=INDEX(A1:A50,MAX((A10:A50<0)*(ROW(A10:A50))))

or

=INDEX(A10:A50,MAX((A10:A50<0)*(ROW(A10:A50)))-ROW(A10)+1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Peo Sjoblom said:
One way

=INDEX(A1:A40,MAX((A1:A40<0)*(ROW(A1:A40))))

entered with ctrl + shift & enter
Click to expand...
....

An alternative that doesn't require either array entry or adjusting indices
and uses only one function call,

=LOOKUP(2,1/(A1:A100<0),A1:A100)
 
Harlan Grove said:
...

An alternative that doesn't require either array entry or adjusting indices
and uses only one function call,

=LOOKUP(2,1/(A1:A100<0),A1:A100)
Click to expand...

I would have thought the intermittant #DIV/0's would have messed up
the sort order of the lookup array, but I guess they are ignored.
Is this a safe construct, or could Excel 2005 start treating #DIV/0
as a value you might want to look up?
 
kcc said:
I would have thought the intermittant #DIV/0's would have messed up
the sort order of the lookup array, but I guess they are ignored.
Is this a safe construct, or could Excel 2005 start treating #DIV/0
as a value you might want to look up?
Click to expand...

There are a lot of constructs Excel 2005/6/7/? (code name 'Longshot'?) could
screw up, and this is certainly one of them. While MATCH, VLOOKUP and
HLOOKUP could be 'fixed', LOOKUP is unlikely to be touched since online help
almost deprecates its use.
 
Back
Top