Using the TREND Function to Forecast Future Values in Excel Charts and Dashboards

Excel

The TREND function will also forecast future points on the trend line. The TREND function format is,

=TREND(known-y’s,[known_x’s],[new_x’s],[constant])

In the figure below the previous tutorial was used to fill D24:O24 with the array formula,

{=TREND(D23:O23,D22:O22)}

The TREND function can also forecast data for future time periods.

The TREND function can also forecast data for future time periods.

Use the TREND function to forecast out as many periods as you think are valid. In general, for most business situations where you are forecasting months, you should probably only forecast two or three months ahead.

The TREND function forecasts Jan through Mar by following these steps,

1. Select P24:R24.
2. Type the function

=TREND(D23:O23,D22:O22,P22:R22)

3. Enter it as an array function by pressing Shift+Ctrl+Enter.

The range will be filled with the straight-line forecast values. Now copy and paste C24:R24 into the chart to add the forecast range.
You can download the Excel workbook containing these basic TREND line charts here. Dynamic Trend Charts – Basic.

Learn how to show or hide a TREND line under the check box control.

Learn how to select between different TREND lines with a drop-down menu.

Learn how to edit a TREND or array function.

Share the power...