Excel Video 103 introduces the TREND function to forecast the next three month’s billed charges. Trend is an array function, so instead of getting the formula right in one cell and copying down, you select all of the cells you want the answer in first. The other thing that makes array functions different is that instead of hitting Enter when you’re finished with the formula, you choose Ctrl+Shift+Enter. Excel will automatically treat the formula as an array. If you see brackets around your formula, that’s Excel’s signal that you’re looking at an array.
You’ll see in Excel Video 103 that the TREND function treats historical data different than future data. Enter the known (historical) data first. If you want to forecast using that data, you need to enter the new x values to forecast with as part of the TREND formula. In the video the historical trend data is shaded blue and the forecasts requiring the additional parameter in the TREND formula are red.
FORECAST, y=mx+b, and TREND were what I wanted to show you about forecasting. Stay tuned next time and we’ll change our trend line to track a moving average instead.
