[Ron Rosenfeld]

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