Finding a break even point in a series of data...

I am doing break-even analysis on a set of data. There is a time series and a number associated with that time. The number starts off negative and eventually becomes positive. I need a formula that will calculate the crossover point, the time at which the data becomes positive.

For example,

1 -10000 1 -12000
2 -8000 2 -10000
3 -4000 3 -6000
4 -2000 4 -3000
5 0 5 -1000
6 1000 6 500
7 10000 7 2000
8 15000 8 6000
9 20000 9 8000
BE is 5 BE is 6


The forumla should execute as quickly as possible. The formula will be applied to an extraordinary amount of data...millions of times.

Thanks for any help I can get.

Hi,
Assuming the data is in col B and starts in B3 then try in col where you want to show Break Even (BE), then try
= IF(and(or(B3=0,B3>0),B2<0),"BE","")
and drag down col
Regards Howard

Hi brazilnut,

Try this one:

=COUNTIF(B1:B9,"<0")+1

- Asser

Jazzer:Thanks but there is one situation where the formula would fail for my situation. That is if the data series starts off negative, turns positive and reverts back to negative. In this situation, I need the value of the formula to equal the FIRST breakeven point, ignoring the second.

Gearcutter: I need the formula to evaluate the data in one cell.

Hi

Try this array entered formula (ctrl, shift, enter)

=MIN(IF((A1:A9<0)*(OFFSET(A1:A9,1,0)>=0),ROW(A1:A9),""))+1

rylo