Linear Regression with Microsoft Excel

!±8± 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)

Linear Regression with Microsoft Excel

Comparison Graco Nautilus Ratings

Variance Calculations with Microsoft Excel

!±8± Variance Calculations with Microsoft Excel

Excel provides useful statistical functions for finding the variance of a data set. In general, these functions variance memorized a set of values ​​called a worksheet range and then calculating the expected.

A detour to remind readers that it is only a common measure to describe the spread of observations in a distribution. A variation is another statistical measure, the relative standard deviation. A deviation is equal to the square of the standard deviation.

Variancea sample of VAR

If the record is to work with a sample, and you do not want to include logical values ​​or text from the amount in the calculation, use the VAR. For example, if you can open a new manufacturing process that should result in increased productivity and a series of data on the number of pieces per day, the variance of the sample. The VAR function uses the following syntax:

= VAR (data set range)

Variance of a samplewith VARA

If the record is an example, but you want to include logical values ​​or text in the calculation, use the VARA function. Excel includes cells containing the logical value TRUE as 1 and cells containing text or FALSE 0 The VARA function uses the following syntax:

= VARA (data set range)

Variance of a sample with VARP

If the record is to work with people, and you do not want to include logical values ​​or textthe amount of calculation, use the VARP. The variance function uses the following syntax:

= VARP (data set range)

Variance of a population with the VARPA

If the record is a population, but you want to include logical values ​​or text in the calculation, use the VARPA. The VARPA uses the following syntax:

= VARPA (data set range)

One last note about the topic Data Range in September

Another tip: you can have moreWorksheet as the set of interval data is sufficient argument for the use of variance functions. For example, if you were to calculate the variance of a sample of VAR and had the data in different ranges saved worksheet, your VAR as follows:

= VAR (B1: B: 10, B101: B110, B200)

Variance Calculations with Microsoft Excel

!8!# Halloween Caramel Apples Best Quality