Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hello everyone;

    i have a simple table in excel (office 2003), and am interested to extrapolate to (let's say, year 2020) using the equation of the linear trendline.
    if i select to plot line with dotted points (top graph), i get one trendline equation, but if i select to plot from XY scatter, i get another trendline equation !!!!!
    both plots of data are similar (seem rather identical), in pattern, however the difference?

    any help?

    TIA.

    daniel rozenberg

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Hard to say for sure without seeing the data, but I'd guess the line chart is treating your X axis values as categories and trending against 1,2,3,4 etc. rather than against the actual values.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi rory;
    thanks for your comment.
    it appears that perhaps i didn't upload the attachment.
    please let me know if it "flies OK" this time
    Attached Files Attached Files

  4. #4
    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
    It is exactly as Rory indicates. In the XY scatter, Excel understands that the X values are numbers (1980, 1985, 1990, etc.) In the line chart, the Xs are categories, not numbers, so to trend it must assign numbers and it uses 1,2,3,4,5,6,7.

    To understnad the relationship in the slopes/intercepts
    The X = (LineX - 1)*5+1980 [X = 1980 when Line-x = 1, X=1985 when Line-X = 2, etc]

    For the XY Scatter, Y = Slope*X + Intercept so for the LineChart:

    Y = Slope*[(LineX-1)*5+1980] + Intercept
    Y = Slope * [5LineX - 5 +1980] + Intercept
    Y = Slope * [5LineX +1975] + Intercept
    Y = (5*Slope)*LineX + (1975*Slope+ Intercept)
    Y = LineSlope*LineX + LineIntercept

    Thus for the line chart, the LineSlope is 5*Slope [=136.42857.. = 5* 27.2857...] and the LineIntercept is 1975*Slope + Intercept [=501.42857...= 1975*27.2857+(-53387.857...)]

    Steve

    PS You can extrapolate with either equation and get the same result.

    For the XY scatter numbers, you can use 2020 directly:
    Y = 27.2857 * 2020 + (-53387.857...) = 1729.2857...

    For the line, you must convert the 2020 to the LineX
    X = (LineX - 1)*5+1980
    X = 5*LineX - 5 + 1980
    X = 5*LineX + 1975
    X - 1975 = 5* LineX
    (X-1975) / 5 = LineX
    Thus
    LineX = (2020 -1975) / 5 = 9
    And in the LineTrend you can use this value:
    Y =136.42857...* 9 + 501.42857...= 1729.2857...

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    Thank you very much for your assistance and explanation.

    Daniel Rozenberg

Posting Permissions

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