Microsoft Office Tutorials and References

In Depth Information

**Using LINEST to Calculate Straight-Line Regression with Complete Statistics**

Figure 14.14.

Figure 14.14.
Using the

Using the SLOPE

functions is a simple way to

calculate a linear regression line.

SLOPE and

and INTERCEPT

INTERCEPT functions is a simple way to

calculate a linear regression line.

The formula in cell E2 calculates the intercept for the line by using

=INTERCEPT(B2:B11,A2:A11). The answer of 49,041 means that the model pre-

dicts that your sales in a hypothetical Year 0 would have been 49,041.

The formula in cell E3 calculates the slope of the line by using

=SLOPE(B2:B11,A2:A11). The answer of 4,230 means that the model predicts

that your sales are increasing by about 4,230 each year.

When you have the slope and y-intercept, you can build a new table to predict

future sales. You enter year numbers 11 through 15 in D8:D12. The formula in

cell E8 needs to multiply the year number by the slope and add the intercept.

That formula is =$E$2+$E$3*D8.

The values in cells E8 through E12 are one prediction of future sales. This

assumes that the past trends continue to work over the next 5 years.

Using

Using
LINEST

LINEST
tto Calculate

Calculate Straight-Line

Straight-Line Regression

Regression with

with Complete

Complete

Statistics

Although SLOPE and INTERCEPT would do the job, the more powerful func-

tion is LINEST. Here is the difficulty: LINEST returns both the slope and

the intercept. In addition, it returns a whole series of statistics. Anytime