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!
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
✓ 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.