Finding a breakeven point in a series of data...

[brazilnut]

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. There are other scenarios that must be accounted for. The data can start negative, turn positive, and revert back to negative. In these situations, I need the value to equal the first breakeven point. Also, the data can start positive and turn negative after a period of time.

For example,

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


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.

 

[mikerickson]

Have you looked at GoalSeek or Solver?

Also =MATCH(0,A:A,0) will return the row number of the first cell in column A that equals 0.

 

[Jon von der Heyden]

Hi

Assuming you do not have an exacting break-even point (i.e. where it is exactly zero), then perhaps one of the following options.

Book2
	B	C	D	E	F	G	H	I	J	K	L	M
2	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
3	-100000	-75000	-50000	-25000	0	25000	50000	75000	100000	125000	150000	175000
4
5
6	may
Analysis

Formula used in B6: =LOOKUP(2,1/(B3:M3<0),C2:M2)

Or you could use the following UDF which returns the break-even point as an index number:

Function BREAKEVEN(Rng As Range)

    Dim i As Long
        
        BREAKEVEN = CVErr(xlErrNA)
        
        For i = Rng.count To 1 Step -1
            If Rng(i)< 0 Then
                BREAKEVEN = i + 1
                Exit For
            End If
        Next i

End Function

 

[mikerickson]

The array formula
=MATCH(0,--(SIGN(A1:A65000)=SIGN(A2:A65001)),0)

might work.

(Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac))