Microsoft Office Tutorials and References

In Depth Information

**Using STEYX to Calculate Standard Regression Error**

Using

Using
STEYX

STEYX
to Calculate Standard Regression Error

to Calculate Standard Regression Error

Standard error is a measure of the quality of a regression line. In rough

terms, the standard error is the size of an error that you might encounter for

any particular point on the line. Smaller errors are better, and larger er-

rors are worse. Standard error can also be used to calculate a confidence

interval for any point.

Syntax

=STEYX(known_y's,known_x's)

The STEYX function returns the standard error of the predicted y value for

each x in the regression. The standard error is a measure of the amount of er-

ror in the prediction of y for an individual x.

The STEYX function takes the following arguments:

•
known_y's

known_y's
—
This is an array or a range of dependent data points.

•
known_x's

known_x's
—
This is an array or a range of independent data points.

The arguments must be either numbers or names, arrays, or references that

contain numbers. If an array or a reference argument contains text, logical

values, or empty cells, those values are ignored; however, cells that con-

tain the value 0 are included. If known_y'sand known_x'sare empty or have

a different number of data points, STEYX returns an #N/A error.

To calculate standard error, you square all the residuals and add them to-

gether. Then you divide by the number of points, excluding the starting and

ending points. Finally, you take the square root of that result to calculate

standard error.

In general, a lower standard error is better than a higher one. A standard er-

ror of 2,000 when you are trying to predict the price of a $30,000 car isn
’
t

too bad. A standard error of 2,000 when you are trying to predict the price of

a $3 jar of pickles is horrible. You need to compare the standard error to

the size of the value you are predicting.

In
Figure 14.22
, two regressions attempt to predict the price of a car based

on either mileage or age. The standard error for the mileage method is a little

less than the standard error for the age method.