Linear Regression with Microsoft Excel

You use Excel's linear regression functions to find a linear equation that best describes a data set.
Excel uses the sum of least squares method to find the straight line of best fit. People often
try to predict future amounts by assuming linear growth and extending the line forward in
time. For example, if you have a series of sales data for 9 months and want to predict the
sales in the 10th month, you can use Excel's linear regression functions to find the slope and
y-intercept (the point on the y-axis where the line crosses) of the line that best fits the data.

Some Background Info on Linear Regression

To use the linear regression functions, it helps to remember the equation for a line:

y=mx+b

where y is the dependent variable, m the slope, x the independent variable, and b the
y-intercept. If there are multiple ranges of x values, the equation looks like this:

y=m1 x1+m2x2+.mnxn+b

NOTE To visualize and experiment with linear regression, visit the interactive web page at
[http://www.math.csusb.edu/faculty/stanton/m262/regress/regress.html]. Click the
graph area to add data points (x,y) to the graph. The applet draws the straight line
that best fits the points you add, adjusting the line for the new data points you add.

Using the FORECAST Function

The FORECAST function predicts a future y-value for the x-value you specify using existing
x and y values. The FORECAST function uses the following syntax:

=FORECAST(x, known ys, known xs)

where x is the x-value for which you want to predict a y-value.

Using the INTERCEPT Function

If you have existing x and y values, Excel can find the straight line that best fits the data and then calculate the point at which the line intersects the y-axis, in other words, the value of b in the "y=mx+b" equation. The y-intercept is useful when you want to know the value of the dependent variable when the independent variable equals 0.

NOTE: The INTERCEPT function returns the same value as the FORECAST function if you enter 0 for x in the FORECAST function.

The INTERCEPT function uses the following syntax:

=INTERCEPT (known ys, known xs)

Using the LINEST Function

The LINEST function returns the value of m and b given at least one set of known ys and known xs. The LINEST function has the following syntax:

=LINEST (known ys, known xs, constant, statistics)

where known ys is the array of y values you already know, known xs is the array of x values you may already know. If you leave out the known xs, they are assumed to be 1, 2, 3,...n. If constant is set to FALSE, b is assumed to be 0. If statistics is set to TRUE, the LINEST function also returns the standard error for each data point.

NOTE: If the known ys are in a single column or row, then Excel considers each column of
known xs to be a separate variable.

NOTE: The array known xs can include multiple sets of variables. If you use only one set, then known ys and known xs can be ranges of any shape, as long as they have equal dimensions. If you use more than one variable, then the known ys array must be either a single column or a single row. If you don't enter known xs, Excel assumes this array is the same size as the known ys array.

Using the SLOPE Function

Use the SLOPE function to find the slope (m) of the linear regression line from the known x and known y data sets. The slope is the change in y over the change in x for any two points on the line. The SLOPE function in Excel uses the following syntax:

=SLOPE (known ys, known xs)

A positive (upwards) slope means that the independent variable (such as the number of salespeople) has a positive effect on a dependent variable (such as sales). A negative (downwards) slope means that the independent variable has a negative effect on the dependent variable. The steeper the slope, the more effect the independent variable has on the dependent variable.

Using the STEYX Function

Use the STEYX function to find the standard error of the predicted y-value for each individual x in the regression. The STEYX function uses the following syntax:

=STEYX (known ys, known xs)

Using the TREND Function

Use the TREND function to find values along a linear trend. Specify an array of new xs and the TREND function uses the method of least squares to fit a straight line to the known x and y data sets and return the y-values along the line for the new array. If constant is set to FALSE, the "b" in the y=mx+b equation is set to zero. The TREND function uses the following syntax:

=TREND (known ys, known xs, new xs, constant)

Hot Game !!!