On Tue, 20 Jan 2009 01:38:56 -0800 (PST), "Helge's"
wrote:

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.

Glad you got it working for you.
--ron