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

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
Fri Jul 09 04:33:55 CDT 2004

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)



"Dmitriy Kopnichev" <kopn@hotbox.ruDELETE> wrote in message
news:%23tugZcYZEHA.808@tk2msftngp13.phx.gbl...
> 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.
>
>

Harlan
Fri Jul 09 05:12:07 CDT 2004

"Peo Sjoblom" <terre08@mvps.org> wrote...
>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
Fri Jul 09 07:21:27 CDT 2004

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:%23UbVtzZZEHA.2520@TK2MSFTNGP12.phx.gbl...
> "Peo Sjoblom" <terre08@mvps.org> wrote...
> >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)
>

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
Fri Jul 09 07:38:06 CDT 2004

"kcc" <kcconline@comcast.NOSPAM.com> wrote...
>"Harlan Grove" <hrlngrv@aol.com> wrote in message
...
>>=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?

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.