Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Predicting with TREND()
The TREND() function is like FORECAST() in that it calculates predictions based on known linear data. It differs in that it calculates predictions of Y for an array of X values rather than for just a single X value. The syntax is as follows:
=TREND(known_y_values, known_x_values, new_x_values, intercept)
- The known_y_values and known_x_values arguments work the same as for the FORECAST() function.
- The new_x_values argument in the range containing an array of X values for which you want to predict Y values.
- Intercept is an optional argument. If omitted or set to True the predictions are calculated normally. If set to False, the predictions are calculated so that the predicted value for Y is 0 when X is 0. This is a special case that you will rarely, if ever, use.
Because TREND() is an array formula, you must enter it with Ctrl+Shift+Enter as described below.
Let’s look at an example of using TREND(). Suppose that you have been keeping track of how many hours it takes to finish a job based on the number of workers you assign to it. The known data and an XY plot of the data are shown in Figure 3.
)
Figure 3 Known data on the relationship between number of workers and length of job
You are interested in predicting the time it will take to do the job if you put 8, 9, or 10 employees to work. Here are the steps to follow:
- Put the X values for which you want predictions in a column of cells, such as B8:B10.
- Select the cells in which you want the predictions displayed; in this example C8:C10.
- Enter the following formula:
=TREND(C3:C8,B3:B8,B10:B12)
- Press Ctrl+Shift+Enter to complete the formula.
The final result is shown in Figure 4. You can see that three Y predictions have been calculated, one for each on the new X values.
)
Figure 4 The predictions calculated by the TREND() function are displayed in cells C10:c12.
Making predictions can be a tough thing to do, but when you have reliable existing data on which to base your predictions, the FORECAST() and TREND() functions can be extremely useful.