scan a series of numbers and when it turns postive give me the yea
 

Hi,

I have two rows; one is a row of years and the other is a series of negative
to positive values (goes from negative then positive) for each year.

i.e.
2002,2003,2004,2005,2006,2007,2008
-100, -75, -33,-12, 5, 8, 50

The values row will change over time based on other formulas. (they're a
series of cash flows)

so if I had to do it manually, based on the sample above, for the first
year the values turned positive, the answer would be year 2006. But of
course the values will change as they are formulas.

So how can I set up a function to give me in a designated cell the first
year that the values turn from negative to positive.

I was thinking hlookup but that depends on a specific value such as 0 so I
am not sure. Can you help?

thank you very much

Craig

scan a series of numbers and when it turns postive give me the yea
 

This relatively simple formula will work if you already have one
positive value.

=INDEX(A1:AZ1,MATCH(0,A2:AZ2,1)+1)

How likely is it to get a 0 before going positive?

HTH
Kostis Vezerides

On Feb 15, 6:43 pm, Craig wrote:
Hi,

I have two rows; one is a row of years and the other is a series of negative
to positive values (goes from negative then positive) for each year.

i.e.
2002,2003,2004,2005,2006,2007,2008
-100, -75, -33,-12, 5, 8, 50

The values row will change over time based on other formulas. (they're a
series of cash flows)

so if I had to do it manually, based on the sample above, for the first
year the values turned positive, the answer would be year 2006. But of
course the values will change as they are formulas.

So how can I set up a function to give me in a designated cell the first
year that the values turn from negative to positive.

I was thinking hlookup but that depends on a specific value such as 0 so I
am not sure. Can you help?

thank you very much

Craig

scan a series of numbers and when it turns postive give me the yea
 

=INDEX(A1:Z1,MATCH(TRUE,A2:Z20,0))

ctrl+shift+enter, not just enter


"Craig" wrote:

Hi,

I have two rows; one is a row of years and the other is a series of negative
to positive values (goes from negative then positive) for each year.

i.e.
2002,2003,2004,2005,2006,2007,2008
-100, -75, -33,-12, 5, 8, 50

The values row will change over time based on other formulas. (they're a
series of cash flows)

so if I had to do it manually, based on the sample above, for the first
year the values turned positive, the answer would be year 2006. But of
course the values will change as they are formulas.

So how can I set up a function to give me in a designated cell the first
year that the values turn from negative to positive.

I was thinking hlookup but that depends on a specific value such as 0 so I
am not sure. Can you help?

thank you very much

Craig