There should be no missing values in the range defined. Otherwise, you

get the error message shown in Figure 156.

Figure 156: Error message if you select an incorrect range for the regression

The statistical Add-In provided with Excel has many

limitations” it does only a few procedures, has bugs, and cannot handle

complex data. (For example, it cannot do a regression if there are any

missing values.) Fortunately, some other companies have created Add-Ins

that provides comprehensive statistics capabilities. Links to such Add-Ins

can be accessed at the URL

http://www.vjbooks.net/products/publications/Excel/Excel.htm..

220

Chapter 12: Regression

I do not show the output or its detailed interpretation. Please

refer to our book “Interpreting regression Output” available at

http://www.vjbooks.net.

A brief summary of interpretation guidelines is presented in the next

section.

BRIEF GUIDELINE FOR INTERPRETING

12.3

REGRESSION OUTPUT

Table 38: Interpreting regression output

Name Of What Does It

Statistic/ Measure Or Critical Values Comment

Chart Indicate?

“ below .01 for

The first statistic to look

99% confidence in

the ability of the for in the output.

model to explain

If Sig.-F is insignificant,

the dependent

then the regression as a

variable

whole has failed. No

Whether the model

more interpretation is

“ below .05 for

as a whole is

95% confidence in necessary (although some

Sig.-F significant. It

the ability of the disagree on this point).

tests whether R-

You must conclude that

model to explain

square is

the dependent the “Dependent variable

significantly

variable cannot be explained by

different from zero

the

“ below 0.1 for independent/explanatory

90% confidence in variables.” The next

the ability of the steps could be rebuilding

model to explain the model, using more

the dependent data points, etc.

variable

The ESS should If the R-squares of two

RSS, ESS & The main function

be high compared models are very similar

TSS of these values lies

to the TSS (the or rounded off to zero or

in calculating test

ratio equals the R- one, then you might

statistics like the

square). Note for prefer to use the F-test

221

Statistical Analysis with Excel

Name Of What Does It

Statistic/ Measure Or Critical Values Comment

Chart Indicate?

F-test, etc. interpreting the formula that uses RSS

table, column and ESS.

“Sum of Squares”:

“Total” =TSS,

“Regression” =

ESS, and

“Residual” = RSS

There is no critical

value. Just

You may wish to

compare the std.

comment on the SE,

error to the mean

SE of The standard error especially if it is too large

of the predicted

or small relative to the

Regression of the estimate

dependent

predicted mean of the

variable. The

dependent variable predicted/estimated

former should be

values of the dependent

small (<10%)

variable.

compared to the

latter.

Proportion of This often mis-used

variation in the value should serve only

dependent variable as a summary measure of

-Square Between 0 and 1.

that can be A higher value is Goodness of Fit. Do not

explained by the better. use it blindly as a

independent criterion for model

variables selection.

Proportion of

variance in the

dependent variable

Another summary

that can be