Microsoft Office Tutorials and References
In Depth Information
Considerations When Using Regression Analysis
judge a line with errors of +400, 300, 100, it would also add up to an error
of 0.
Instead, the regression engine sums the square of each error. In this case, the
first line would have an error of 2^2 + 1^2+ 1^2 or 4 + 1 + 1, or 6. The
second line would have an error of 400^2+ 300^2 + 100^2 or 160,000 + 90,000
+ 10,000, or 260,000. With this method, the error for the first line is clearly
better than the error for the second line. This method is called the least-
squares method.
You might wonder why regression doesn t add the absolute value of each er-
ror. Ideally, the errors around the regression line should be narrow. A line
with errors of 4, +4, 4, +4 would result in a sum of squares of 64. A line
with errors of 7, 1, 7, 1 would result in a sum of squares of 100. The sum of
squares method would deem the earlier line to be better, whereas using abso-
lute values would call them equal.
Considerations When Using Regression Analysis
You need to consider one question before doing regression analysis: Is the
data series growing linearly or exponentially? Sales for a company might
grow linearly. The number of bacteria cells in a Petri dish might grow expo-
nentially. You use LINEST and TREND to predict sales that are growing lin-
early. You use LOGEST and GROWTH to predict bacteria that are growing ex-
ponentially.
In Figure 14.12 , the chart on the left shows sales over time. These sales are
growing linearly and could probably be predicted fairly well by a straight
line. The dotted line in the chart is the straight-line regression for the data
set. Although each data point is either above or below the regression line,
the error at any given data point is fairly small.
Search JabSto ::

Custom Search