| You use Excel's linear regression functions | | | | and b given at least one set of known ys and |
| to find a linear equation that best describes | | | | known xs. The LINEST function has the |
| a data set. | | | | following syntax: |
| | | | |
| Excel uses the sum of least squares method to | | | | =LINEST (known ys, known xs, constant, |
| find the straight line of best fit. People | | | | statistics)where known ys is the array of y |
| oftentry to predict future amounts by | | | | values you already know, known xs is the |
| assuming linear growth and extending the line | | | | array of x values you may already know. If |
| forward intime. For example, if you have a | | | | you leave out the known xs, they are assumed |
| series of sales data for 9 months and want to | | | | to be 1, 2, 3,…n. If constant is set to |
| predict thesales in the 10th month, you can | | | | FALSE, b is assumed to be 0. If statistics is |
| use Excel's linear regression functions to | | | | set to TRUE, the LINEST function also returns |
| find the slope andy-intercept (the point on | | | | the standard error for each data point. |
| the y-axis where the line crosses) of the | | | | |
| line that best fits the data. | | | | NOTE: If the known ys are in a single column |
| | | | or row, then Excel considers each column |
| Some Background Info on Linear Regression | | | | ofknown xs to be a separate variable. |
| | | | |
| To use the linear regression functions, it | | | | NOTE: The array known xs can include multiple |
| helps to remember the equation for a | | | | sets of variables. If you use only one set, |
| line:y=mx+bwhere y is the dependent variable, | | | | then known ys and known xs can be ranges of |
| m the slope, x the independent variable, and | | | | any shape, as long as they have equal |
| b they-intercept. If there are multiple | | | | dimensions. If you use more than one |
| ranges of x values, the equation looks like | | | | variable, then the known ys array must be |
| this:y=m1 x1+m2x2+.mnxn+b | | | | either a single column or a single row. If |
| | | | you don't enter known xs, Excel assumes this |
| NOTE To visualize and experiment with linear | | | | array is the same size as the known ys array. |
| regression, visit the interactive web page at | | | | |
| Click thegraph area to add data points (x,y) | | | | Using the SLOPE Function |
| to the graph. The applet draws the straight | | | | |
| linethat best fits the points you add, | | | | Use the SLOPE function to find the slope (m) |
| adjusting the line for the new data points | | | | of the linear regression line from the known |
| you add. | | | | x and known y data sets. The slope is the |
| | | | change in y over the change in x for any two |
| Using the FORECAST Function | | | | points on the line. The SLOPE function in |
| | | | Excel uses the following syntax: |
| The FORECAST function predicts a future | | | | |
| y-value for the x-value you specify using | | | | =SLOPE (known ys, known xs) |
| existingx and y values. The FORECAST function | | | | |
| uses the following syntax: | | | | A positive (upwards) slope means that the |
| | | | independent variable (such as the number of |
| =FORECAST(x, known ys, known xs)where x is | | | | salespeople) has a positive effect on a |
| the x-value for which you want to predict a | | | | dependent variable (such as sales). A |
| y-value. | | | | negative (downwards) slope means that the |
| | | | independent variable has a negative effect on |
| Using the INTERCEPT Function | | | | the dependent variable. The steeper the |
| | | | slope, the more effect the independent |
| If you have existing x and y values, Excel | | | | variable has on the dependent variable. |
| can find the straight line that best fits the | | | | |
| data and then calculate the point at which | | | | Using the STEYX Function |
| the line intersects the y-axis, in other | | | | |
| words, the value of b in the "y=mx+b" | | | | Use the STEYX function to find the standard |
| equation. The y-intercept is useful when you | | | | error of the predicted y-value for each |
| want to know the value of the dependent | | | | individual x in the regression. The STEYX |
| variable when the independent variable equals | | | | function uses the following syntax: |
| 0. | | | | |
| | | | =STEYX (known ys, known xs) |
| NOTE: The INTERCEPT function returns the same | | | | |
| value as the FORECAST function if you enter 0 | | | | Using the TREND Function |
| for x in the FORECAST function. | | | | |
| | | | Use the TREND function to find values along a |
| The INTERCEPT function uses the following | | | | linear trend. Specify an array of new xs and |
| syntax: | | | | the TREND function uses the method of least |
| | | | squares to fit a straight line to the known x |
| =INTERCEPT (known ys, known xs) | | | | and y data sets and return the y-values along |
| | | | the line for the new array. If constant is |
| Using the LINEST Function | | | | set to FALSE, the "b" in the y=mx+b equation |
| | | | is set to zero. |
| The LINEST function returns the value of m | | | | |