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

[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.

 

[Peo Sjoblom]

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)

 

[Harlan Grove]

One way

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

entered with ctrl + shift & enter

....

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)

 

[kcc]

...

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)

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?

 

[Harlan Grove]

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?

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.