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

#### brazilnut

##### New Member
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.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### mikerickson

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
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
BCDEFGHIJKLM
2janfebmaraprmayjunjulaugsepoctnovdec
3-100000-75000-50000-250000250005000075000100000125000150000175000
4
5
6may
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:
Code:
``````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

##### MrExcel MVP
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))

Replies
1
Views
292
Replies
3
Views
921
Replies
2
Views
301
Replies
1
Views
393
Replies
1
Views
352

1,191,204
Messages
5,985,261
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back