Microsoft Office Tutorials and References

In Depth Information

**Using FORECAST to Calculate Prediction for Any One Data Point**

for the final independent variable (
−
102.236). If you enter LINEST and receive

just one value, you should follow these steps:

11. Select a range starting with the LINEST formula in the upper-left

corner. The range should be five rows tall. It should be at least two

columns wide for models with one known_xcolumn. Add additional

columns for additional known_xseries.

22. Press the F2 key to edit the current LINEST formula.

33. Hold down Ctrl+Shift+Enter to reenter the formula as an array.

Alternatively, you can use the INDEX function to pluck one particular

value out of the LINEST function. For example, if you want to retrieve

the

F

statistic

from

row

4,

column

1,

you

could

use

=INDEX(LINEST(E4:E95,B4:D95,TRUE,TRUE),4,1).

In the simpler situation when you have only one independent x variable, you

can obtain the slope and y-intercept values directly by using the following

formula for slope:

INDEX(LINEST(known_y's,known_x's),1)

Use the following formula for the y-intercept:

INDEX(LINEST(known_y's,known_x's),2)

Using

Using
FORECAST

to Calculate Prediction for Any One Data Point

When you understand straight-line regression, you can use the FORECAST

function to return a prediction for any point in the future.

FORECAST
to Calculate Prediction for Any One Data Point

Note

Note that FORECAST works only for straight-line regression. It

also does not offer the capability to force the intercept to be 0. If you

need this capability, you must use LINEST and then build a prediction

formula as in step 14 of the previous section or the TREND function,

as discussed in the next section.

Syntax

=FORECAST(x,known_y's,known_x's)