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.