Microsoft Office Tutorials and References

In Depth Information

**What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions**

After formatting the cell as Currency, the result shown in Figure 11-5 displays

the prediction that your new salesman will make $27,093 in sales his first

year. But remember: This is just a prediction and not a guarantee!

TREND

You just saw how the FORECAST function can predict a Y value for a known

X based on an existing set of linear X-Y data. What if you have more than

one X value to predict? Have no fear, TREND is here! What FORECAST does

for a single X value, TREND does for a whole array of X values.

Like FORECAST, the TREND function is intended for working with linear data.

If you try to use it with nonlinear data, the results will be incorrect.

The TREND function takes up to four arguments:

✓
The first argument is a range containing the known Y values.

✓
The second argument is a range containing the known X values.

✓
The third argument is a range containing the X values that you want

predictions for.

✓
The fourth argument is a logical value. It tells the function whether to

force the constant
b to 0. If the fourth argument is TRUE or omitted, the

linear regression line (used to predict Y values) is calculated normally.

If this argument is FALSE, the linear regression line is calculated to go

through the origin (where both X and Y are 0).

Note that the ranges of known X and Y values must be the same size (contain

the same number of values).

TREND returns an array of values, one predicted Y for each X value. In other

words, it’s an array function and must be treated as such. (See Chapter 3 for

help with array functions.) Specifically, this means selecting the range where

you want the array formula results, typing in the formula, and pressing Ctrl +

Shift + Enter rather than pressing Enter alone to complete the formula.

When would you use the TREND function? Here’s an example: You’ve started

a part-time business, and your income has grown steadily over the past 12

months. The growth seems to be linear, and you want to predict how much

you will earn in the coming six months. The TREND function is ideal for this

situation. Here’s how to do it:

1. In a new worksheet, put the numbers 1 through 12, representing the

past 12 months, in a column.

2. In the adjacent cells, place the income figure for each of these months.