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. So how do these look when you calculate the forecasted y’s & graph them all together? Seeing a pattern? In order to find an n’th order polynomial, you simply use the coefficient equation n = INDEX(LINEST(y,x^),1,n) 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.Īgain, 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. 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. 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. Getting back to our Linear equation, y = m*x+b, the equations for the coefficients m & b are: For the rest of the examples below, there are no built-in formulas.įor 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
To calculate these coefficients, Excel has built-in formulas, SLOPE & INTERCEPT. 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. The equation for a linear line is y = m*x+b. The most simple of trend lines is to “fit” a linear line to your data. This post will assume that you know your x values and are trying to forecast your y values. Rarely will you have to forecast both x & y. Typically, you will have x or y values and need to calculate what the corresponding x or y values are.
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. 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. Excel graphs can automatically add trend lines based on all of these trend methods. I will show examples of the trended data graphed against the original data.
This post is on how to manually calculate trend lines in Excel.
0 kommentar(er)
