Linear Regression With Microsoft Excel

You use Excel's linear regression functions to find astatistics)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 findmay already know. If you leave out the known xs,
the straight line of best fit. People oftentry to predictthey are assumed to be 1, 2, 3,…n. If constant is
future amounts by assuming linear growth andset to FALSE, b is assumed to be 0. If statistics is set
extending the line forward intime. For example, if youto TRUE, the LINEST function also returns the
have a series of sales data for 9 months and want tostandard error for each data point.
predict thesales in the 10th month, you can use Excel'sNOTE: If the known ys are in a single column or row,
linear regression functions to find the slopethen Excel considers each column ofknown xs to be a
andy-intercept (the point on the y-axis where the lineseparate variable.
crosses) of the line that best fits the data.NOTE: The array known xs can include multiple sets
Some Background Info on Linear Regressionof variables. If you use only one set, then known ys
To use the linear regression functions, it helps toand known xs can be ranges of any shape, as long as
remember the equation for a line:y=mx+bwhere y isthey have equal dimensions. If you use more than one
the dependent variable, m the slope, x the independentvariable, then the known ys array must be either a
variable, and b they-intercept. If there are multiplesingle column or a single row. If you don't enter known
ranges of x values, the equation looks like this:y=m1xs, Excel assumes this array is the same size as the
x1+m2x2+.mnxn+bknown ys array.
NOTE To visualize and experiment with linearUsing the SLOPE Function
regression, visit the interactive web page at ClickUse 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 thedata sets. The slope is the change in y over the
points you add, adjusting the line for the new datachange 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 forA 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 thevariable (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 Functionnegative effect on the dependent variable. The
If you have existing x and y values, Excel can find thesteeper the slope, the more effect the independent
straight line that best fits the data and then calculatevariable has on the dependent variable.
the point at which the line intersects the y-axis, in otherUsing the STEYX Function
words, the value of b in the "y=mx+b" equation. TheUse the STEYX function to find the standard error of
y-intercept is useful when you want to know the valuethe predicted y-value for each individual x in the
of the dependent variable when the independentregression. 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 xUsing 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 Functionstraight line to the known x and y data sets and return
The LINEST function returns the value of m and bthe 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,