Microsoft Office Development and Consultancy

Excel

What is a Regression ?

Regression attempts to show the relationship between two variables by providing a mean line which best indicates the trend of the co-ordinates.

The Regression analysis tool performs linear regression analysis by using the "least squares" method to fit a line through a set of observations.

You can analyze how a single dependent variable is affected by the values of one or more independent variables.

It can be shown mathematically that the best line is one that minimises the total of the squared deviations. This is known as the method of least squares.

All linear regressions take the equation y = mx + b

Using Regression

??

Lets assume we have the following table of data.

Select (Tools > Data Analysis) and choose Regression in the Data Analysis dialog box.

Press OK to display the Regression dialog box.

Specify the input Y range as C2:C8 and the input X range as D2:D8.

Specify the output to be pasted in cell "B11" below this table.

Input X Range - Enter the reference for the range of independent data. Microsoft Excel orders independent variables from this range in ascending order from left to right. The maximum number of independent variables is 16.

Labels - Select if the first row or column of your input range or ranges contains labels. Clear if your input has no labels; Excel generates appropriate data labels for the output table.

Confidence Level - Select to include an additional level in the summary output table. In the box, enter the confidence level you want applied in addition to the default 95 percent level.

Constant is Zero - Select to force the regression line to pass through the origin.

Output Range - Enter the reference for the upper-left cell of the output table. Allow at least seven columns for the summary output table, which includes an anova table, coefficients, standard error of y estimate, r2 values, number of observations, and standard error of coefficients.

New Worksheet Ply - Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook - Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Residuals - Select to include residuals in the residuals output table.

Standardized Residuals - Select to include standardized residuals in the residuals output table.

Residual Plots - Select to generate a chart for each independent variable versus the residual.

Line Fit Plots - Select to generate a chart for predicted values versus the observed values.

Normal Probability Plots - Select to generate a chart that plots normal probability.

This generates the following output

Multiple R - This is the correlation coefficient and is the square root of "R Square".

R Square - The square of the correlation coefficient.

Adjusted R Square -

Standard Error - This should really be labelled as "estimated standard deviation of errors"

Observations - The number of data points.

Line Fit Plots