Identify Breakeven Point, Or Positive To Negative, On Chart

Re: Insert A Line Where Chart Is Breakeven (or Changes From Positive To Negative)

Add another (or several more) series, and compute the endpoints based on the x data.

It might be simpler to use VBA than formulas to detect the transitions of interest.

Re: Insert A Line Where Chart Is Breakeven (or Changes From Positive To Negative)

How about as in the attached. Plot chart as x-y rather than Line. Use TREND function to calculate when the line between the two points is zero. Add point to chart as new series. Add error bars and format as required.

Dave

Re: Insert A Line Where Chart Is Breakeven (or Changes From Positive To Negative)

Hi Robert,

It's still not very automated, as you still need to decide which two points to use to determine the result of the TREND function. I'm sure someone else will be able to come up with a better solution.

Dave

Re: Insert A Line Where Chart Is Breakeven (or Changes From Positive To Negative)

Dave, your use of TREND and error bars is very clever.

Hgus, here's another way.

Re: Identify Breakeven Point, Or Positive To Negative, On Chart

Here's a non-VBA way to hopefully determine all the breakeven points on the chart automatically. My previous attempt still required user-decisions.
The formula in column C,

=IF(AND(B6>0,B7<0),TREND(A6:A7,B6:B7,D6),NA())

basically decides if there is a positive to negative change between adjacent cells, and if there is, determines where the line would cross the x axis, and plots the point. Add Y error bars and format appropriately.

A set of dummy data puts tickmarks at the bottom and it's done, (I think!).

The data is generated using RANDBETWEEN, so press F9 to see how it updates.

Dave

Re: Identify Breakeven Point, Or Positive To Negative, On Chart

Dave, that is VERY clever -- Andy Pope clever!

I changed the 'detector' formula to [COLOR="Blue"]=IF( SIGN(B6) <> SIGN(B7), TREND(A6:A7, B6:B7, D6), NA())[/COLOR] so it picks up zero values in addition to transitions through zero.

Re: Identify Breakeven Point, Or Positive To Negative, On Chart

Very good. Like the use of TREND to calculate crossing point.

If you move the second series to the secondary axis you can use the secondary X axis to provide labels and tickmarks at the bottom, instead of the additional series.

Re: Identify Breakeven Point, Or Positive To Negative, On Chart

Glad to help, Robert.

Thanks for the axis tip Andy. It's easy for us laymen to miss the obvious.

And thanks for the encouragement. Almost everything I know about charting I picked up from my charting hero. (That's you, by the way!)

Dave

Re: Identify Breakeven Point, Or Positive To Negative, On Chart

You have learned from one of the best then