Search code examples
excelexcel-charts

Regression Line in different Excel Charts

I have the following Excel spreadsheet:

       A                B           C         D            E           F
1    X-Values       Y-Values      
2    Year           Revenue         X-Ø       Y-Ø    (X-Ø) x (Y-Ø)  (X-Ø)^2
3    2012              10          -1.5       -55        82.5       2.25
4    2013              50          -0.5       -15         7.5       0.25
5    2014              80           0.5        15         7.5       0.25
6    2015             120           1.5        55        82.5       2.25
7
8    2013.5            65                                 180        5.0
9
10   Slope:               36.00 (=180/5.0)
11   Y-Intercept:    -72,421    (=65 - 36 x 2013.5)
12

In Area A3:B6 you can see the Year and the corresponding Revenue. Now I want to make a simple trend analysis for it. Therefore, I manually calculated the Regression Line in Area C3:F6 and in Cell B10 (Slope) and Cell B11 (Y-Intercept) which leads to the following formula:

y = 36X - 72,421

Now I want to do the same thing with an Excel chart. First I put the data in Area A3:B6 in a Scatter Chart and I got the following formula for the Regression Line: (Matches exactly the manual calculated formula)

Scatter Chart:

enter image description here

When I put the same data into a Line Chart I get a different formula for the Regression Line:

Line Chart:

enter image description here

Why does the formula from the Line Chart differ from the Scatter Chart and how is it calculated?

Solution

  • On the scatter chart the value of x is the value for the year, so the values are 2012, 2013, 2014, 2015.

    On the line chart the values of x are 1, 2, 3, 4, with the year being the label for the x value.