On 20 Jan, 03:34, Ron Rosenfeld wrote:
On Mon, 19 Jan 2009 11:58:49 -0800 (PST), "Helge's"
wrote:





On 19 Jan, 13:12, Ron Rosenfeld wrote:
On Mon, 19 Jan 2009 01:02:02 -0800 (PST), "Helge's"
wrote:

It shall start the first place with a number (different from 0). Next
dataset might have a number in the 5th cell. The last cell (in this
case is 30) is the last cell with a postiv number. In this case it
continue with only negative values (-20).

The following, entered as an **array** formula (confirmed by holding down
<ctrl-shift while hitting <enter ) will generate NPV based on the values from
the first non-zero number to the last positive value.

Rate is either a % or a cell reference containing the interest rate you want to
use.

=NPV(Rate,OFFSET(rng,MATCH(TRUE,rng<0,0)-1,0,
MAX((rng=LOOKUP(2,1/(rng0),rng))*ROW(rng))-
MATCH(TRUE,rng<0,0)+1,1))
--ron

The formula looks very good, but it seems to not cut on the last
positive value. I do not understand the rng=LOOKUP. What are you
looking up. Why are you using 1/(rng0)?

OK, I see a problem with the formula when rng does not start in Row 1. *The
following modification should take care of that -- also an array formula:

=NPV(5%,OFFSET(rng,MATCH(TRUE,rng<0,0)-1,0,
MAX((rng=LOOKUP(2,1/(rng0),rng))*ROW(rng))-
ROW(rng)-MATCH(TRUE,rng<0,0)+2,1))

--ron– Skjul sitert tekst –

– Vis sitert tekst –

Thank you very much. I modified the formula to this: =NPV($M$9;OFFSET
(rng;MATCH(TRUE;rng<0;0)-1;0;MAX((rng=LOOKUP(2;1/(rng0);rng))*(ROW
(rng)))-(MIN(ROW(rng)+1)))). It work. You know I have to translate the
formula to norwegian. We are also using semicolon instead of comma to
separate the arguments. Is amazing what an arrayformula can do. Thanks
again.