Microsoft Office Tutorials and References
In Depth Information
Using STEYX to Calculate Standard Regression Error
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.
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 — This is an array or a range of dependent data points.
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
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.