Identify Breakeven Point, Or Positive To Negative, On Chart

  • Hi all,


    I wonder if there is an easy way to insert a Line where the (x) line on a chart is breakeven or the instance where it changes from positive to negative. I am doing this now by just drawing a line, however with a chart that is constantly updating the line does not adjust to this.


    Clues anyone?


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • 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.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

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


    Quote from shg

    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.


    Eh, ok I am a rookie at charting but I have posted what I would like it to look like...The vertical line is a drawn line.
    Robert

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


    Quote from davecurtis

    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


    Nice!!! That is awsome :o), thanx a lot!


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • 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: 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.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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


    I concur,
    :wowee:
    Dave, a very nice solution which I have implemented into my graphs.


    Many thanx


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!