This post is on how to manually calculate trend lines in Excel. I will show examples of the trended data graphed against the original data. Excel graphs can automatically add trend lines based on all of these trend methods. However, this post will show you how to manually calculate all of them should you want to forecast a trend line past the known data set to calculate predicted values for x & y.
Please note that this post will not show you how to figure out which trend line is best for forecasting future data, however we will discuss figuring out which trend line best correlates to current data.
Typically, you will have x or y values and need to calculate what the corresponding x or y values are. Rarely will you have to forecast both x & y. This post will assume that you know your x values and are trying to forecast your y values.
The most simple of trend lines is to “fit” a linear line to your data. The equation for a linear line is y = m*x+b.
The coefficients for this equation are m & b where m is the slope of the line and b is where the line intercepts the y-axis. To calculate these coefficients, Excel has built-in formulas, SLOPE & INTERCEPT. For the rest of the examples below, there are no built-in formulas.
For each type of trend line, I will present the coefficients, the equation to calculate each coefficient and then the calculated value based on the following data set:
| X | Y |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 5.5 |
| 4 | 9 |
| 5 | 10 |
| 6 | 14 |
| 7 | 13 |
| 8 | 14 |
| 9 | 14 |
| 10 | 17 |
Getting back to our Linear equation, y = m*x+b, the equations for the coefficients m & b are:
m = SLOPE(y,x)
b = INTERCEPT(y,x)
where y are your known y’s & x are your known x’s.
To forecast y values for your known x’s you then simply plug these coefficient values into the equation for a line and solve for y.
Logarithmic Trend Line
Logarithmic Equation: y=(c*LN(x))+b
where:
c = INDEX(LINEST(y,LN(x)),1)
b = INDEX(LINEST(y,LN(x)),1,2)
where y are your known y’s & x are your known x’s. In addition, these formulas are entered using CTRL+SHIFT+ENTER to enter them as an array formula – otherwise the function will not work correctly – it expects an array of data.
Exponential Trend Line
Exponential Equation: y=c*e^(b*x)
where:
c = EXP(INDEX(LINEST(LN(y),x),1,2)
b = INDEX(LINEST(LN(y),x),1)
Where again, y are your known y’s & x are your known x’s and these formulas are entered using CTRL+SHIFT+ENTER to enter them as an array formula.
Power Trend Line
Power Equation: y=c*x^b
where:
c = EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)
b = INDEX(LINEST(LN(y),LN(x),,),1)
Again, y are your known y’s & x are your known x’s and these formulas are entered using CTRL+SHIFT+ENTER to enter them as an array formula.
2nd Order Polynomial Trend Line
Power Equation: y=(a*x^2)+(b*x)+c
where:
a = INDEX(LINEST(y,x^{1,2}),1)
b = INDEX(LINEST(y,x^{1,2}),1,2)
c = INDEX(LINEST(y,x^{1,2}),1,3)
Again, y are your known y’s & x are your known x’s and these formulas are entered using CTRL+SHIFT+ENTER to enter them as an array formula.
3rd Order Polynomial Trend Line
Power Equation: y=(a*x^3)+(b*x^2)+(c*x)+d
where:
a = INDEX(LINEST(y,x^{1,2,3}),1)
b = INDEX(LINEST(y,x^{1,2,3}),1,2)
c = INDEX(LINEST(y,x^{1,2,3}),1,3)
d = INDEX(LINEST(y,x^{1,2,3}),1,4)
Again, y are your known y’s & x are your known x’s and these formulas are entered using CTRL+SHIFT+ENTER to enter them as an array formula.
Seeing a pattern? In order to find an n’th order polynomial, you simply use the coefficient equation n = INDEX(LINEST(y,x^{1,2,3…n}),1,n)
So how do these look when you calculate the forecasted y’s & graph them all together?
(Click on picture to enlarge)
So how do you know which type of trend line is “better”? Better can be subjective however one way to decide is to see how close your trended y values fit the y values of your original line. You can use the CORREL function in excel to find out how correlated your trended y values are to your original y values. In this instance, the 3rd Order Polynomial Trendline has the largest correlation (1 = perfect correlation, 0 = no correlation) indicating that it’s the best “fit” to our original data. Unfortunately, this only works for the known y’s. As you can see from the picture above – the polynomial equations have started to curve over to the right and flatten out, whereas the original data points seem to indicate an upward trend. How to choose a forecasting method will have to wait for another post.
Questions? Did I make a mistake? Let me know in the comments below!
Excellent Tips 