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
Search JabSto ::




Custom Search