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:
- Asser=COUNTIF(B1:B9,"<0")+1
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
Brazilnut,
Please read the link about crossposting below. It explains why crossposting, without notification, is annoying and against this forum's rules.
When I got no feed-back about the array formula
=MATCH(0,--(SIGN(A1:A65000)=SIGN(A2:A65001)),0)
posted at http://www.mrexcel.com/forum/showthread.php?t=327730, I assumed that you found it sufficiant, but you were too busy completing your project to let us know that.
It seems that that was not the case.
If a suggestion doesn't work for you, posting that failure, along with how it fails you, encourages people to modify things until a solution is found.
If a suggestion works, posting that will help those who use the forums' search features.
When a questioner abandons a thread, it get them a reputation. And people will be less willing to help that questioner in the future.
Last edited by mikerickson; 07-01-2008 at 10:05 PM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks