Selecting a Range in Excel: TREND
If you’re going to use regression to forecast sales, you should use TREND to obtain the actual forecasts and LINEST to obtain the regression equation itself as well as additional statistics that inform you about the quality of the equation. These additional statistics include R-squared, which tells you how much variability in the sales figures is shared with the predictor variable or variables.
It’s important to use both functions. TREND helps out by relieving you of having to do the arithmetic involved in applying the regression equation. LINEST gives you useful information about whether your forecasts are likely accurate, acceptable, or random garbage.
Your raw sales data is likely in the form of an Excel table or pivot table, or possibly a list.
A list is just data in a rectangular range. A table is a list that’s been enhanced with filter and sort capabilities, a row at the bottom that can show data summaries, and various other handy tools.
If your raw data is oriented in that way, with different variables in different columns and different records in different rows, you’ll begin to array-enter the TREND function by selecting a range that’s one column wide and with as many rows as you have records in the data source. There’s only one variable to be forecast, so only one column is needed. You want to know the forecast value for each record, so your TREND range needs as many rows as there are records.
However, most of the forecasts from TREND are for periods that have already passed. You have no special need for those forecasts except as a way to assess the accuracy of the regression equation. So this form of an array formula that uses the TREND function calls for only the known values of the predictor(s) and the variable to be predicted:
See the following figure. The range D2:D21 contains that TREND function in its array formula, but it does not forecast the subsequent predictor value in cell A22. It does return the forecasts for the first 19 records, which are useful to have on the worksheet because they can be charted against the actual sales results. The chart gives you a visual sense of the accuracy of the regression equation in generating forecasts.
To get the next forecast value, whose actual will eventually occupy cell B21, you need to supply the next predictor value as TREND’s third argument:
So, you need to array-enter the first of the two previous formulas in D2:D21 and the second in D22. Knowing what you’re telling TREND to do is essential for knowing the size and orientation of the range into which you’ll enter the array formulas.
You could enter the formula in D22 normally. It occupies one cell only and it calls a function that expects arrays as its arguments. Instead, array-enter the formula. There’s likely to come a time when you have more than just one cell as a true future value: for example, A22:A25 instead of just A22. Then you’ll need to array-enter the formula, and you might as well get used to doing so.