Add vertical line to chart
Results 1 to 4 of 4

Thread: Add vertical line to chart
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,282
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Add vertical line to chart

    Hi,

    I have a graph based on a 3-column data table where

    x- axis is time (months, Jun 2019 to Dec 2020)
    Primary y-axis is account balance ($0 to $3.5m)
    Secondary y-axis is PnL (-$300k to $200k)

    August 2020 is first month PnL > $0 (Aug 2020 as the answer is calculated in a different cell)

    How can I add a vertical line (or other indicator) to the X-axis at point August 2020 or where the line changes from -ve to +ve on the secondary axis?

    TIA,
    Jack
    Last edited by JackDanIce; Aug 8th, 2019 at 08:04 AM.


  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add vertical line to chart

    Here's one way which might work for you . . .

    It requires you to be MANUALLY setting the Y axis minimum and maximum values, rather than leaving them to be set automatically.

    Set up another chart series, and set up the data that drives that series so that BEFORE the point where you want the vertical line to appear, its value is far BELOW the Y axis minimum values, and AFTER the point where you want it to appear, its value is far ABOVE the Y axis maximum values.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Jun 2019
    Location
    Mississauga, ON, Canada
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add vertical line to chart

    You can also set this up so that the breakeven line moves based on the data. Here is an example sheet and chart.



    I've added another data series (BrkLine), plotted on the secondary axis, for the breakeven line. The x-axis and values for this data series are based on the data.

    To know where the PnL series goes from negative to positive, I used a helper column, column F, with the cells having the formula similar to =IF(AND(C16>0,C15<0),1,0) (this is for cell F16) so there is a 1 in only the month that it turns positive.

    Then cell A22 has the formula =INDEX(A3:F21,MATCH(1,F3:F21,0),1) so it is set to the same month that the PnL turned positive. Cell A23 is =A22.
    For the values for the BrkLine data series, i used 80% of the minimum value of the PnL series in D22 and 80% of the maximum for the PnL series in D23. This keeps the line within the vertical space of the secondary axis and adjusts if the data changes.

    This way, the breakeven line moves and gets longer or shorter as the data changes.

    One last thing I did is change the setting in the Hidden and Empty Cells dialog box in the Select Data dialog box to have Excel connect data points if there is am empty cell. This keeps the Balance and PnL lines unbroken.

    Hope this helps.

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,282
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Add vertical line to chart

    Thanks both @Gerald Higgins and @Dave Paradi, Dave I will adapt your approach as I believe it'll fit my setup up easier and I'm already using an almost equivalent formula to find the month breakeven occurs in.

    FYI for the helper column, try this formula instead:
    Code:
    =--(AND(C15<0,C16>=0)
    "--" converts TRUE into 1 and FALSE into 0, yes my mistake I did mean including breakeven, i.e. 0 or higher, NOT when profit is only positive (i .e. > 0)

    Cheers,
    Jack
    Last edited by JackDanIce; Aug 8th, 2019 at 12:27 PM.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •