# Thread: confused with trendline different equations

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

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

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