[Ron Rosenfeld]

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)?

It worked properly here on the data set you provided.
How did you define rng?
Did you enter this as an array formula (i.e. did Excel place braces {...}
around the formula after you entered it)?

LOOKUP(2,1/(rng0),rng) returns the last value in rng that contains a value
greater than 0.

rng=LOOKUP(2,1/(rng0),rng) returns an array of TRUE and FALSE depending on
whether or not a value in rng matches the last positive number. You have to do
this because there is no guarantee that the last positive value will be unique.

(rng=LOOKUP(2,1/(rng0),rng))*ROW(rng) an array of either 0's, or the row
numbers that contain that last positive value.

MAX((rng=LOOKUP(2,1/(rng0),rng))*ROW(rng)) returns the highest numbered row
value that contains the last positive number.
--ron