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