Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multiple trendlines on a graph (excel xp)

    If i have a line graph showing 36 months of data, is there a way i can have 3 different trend lines on one graph. I want 1 trendline to show the trend from month 1 to month 12. The next to show month 13 to 24 and the third to show 25 to 36. Thanks for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple trendlines on a graph (excel xp)

    1) if you are going to do a trend, I recommend an XY scatter not a line chart. In a line chart, excel assumes the "X-axis" is a category and NOT numbers

    2) To add the multiple trend lines:
    create extra columns of data with the ranges you want, (You can use an IF statement, to read the value or an NA()).
    Something like:
    <pre>=if(and(month(a1)>=1, month(a1)<=12),B1,na())
    =if(and(month(a1)>=13, month(a1)<=24),B1,na())
    =if(and(month(a1)>=25, month(a1)<=36),B1,na())
    </pre>

    Add these 3 datasets to the chart (You should have 4 "value axes".
    add trendlines for the 3 Subsets
    To NOT display the subsets: select no line and no marker from the format series

    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple trendlines on a graph (excel xp)

    You don't have to create another column to plot another series. Say your original data has the Xvalues in A1..A60 with the Yvalues in B1..B60. Create the XY chart with that series. Then select the cells containing the first 12 months (say A1..A20 and B1..B20), copy and paste them into the chart. Select that series (by using the cursor if necessary) format as no lines and no points, and create the trend-line.
    Does this make sense?
    RuthC

Posting Permissions

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