How to Use FORECAST Function with Multiple Variables in Excel (6 Methods)

Let’s break down the methods for using the FORECAST function with multiple variables in Excel to determine Sales for the year 2022. A concise summary for each method will be provided:

Sample dataset for using FORECAST Function

Method 1 – Using the FORECAST Function with Multiple Variables

Enter the following formula in cell C12.

=FORECAST(B12, C5:C11, B5:B11)

Using FORECAST Function

Formula Breakdown

FORECAST(B12, C5:C11, B5:B11) → The FORECAST function determines the future value based on a current value.
B12 → Current value of the Year (2022).
C5:C11 → Known range of Y values (Sales).
B5:B11 → Known X value (Years).
- Output: $20971

Press ENTER.
Cell C12 will display the result: $20971.

Method 2 – Applying the FORECAST.LINEAR Function

Enter the following formula in cell C12.

=FORECAST.LINEAR(B12,C5:C11,B5:B11)

Formula Breakdown

LINEAR(B12,C5:C11,B5:B11) → the FORECAST.LINEAR function determines the future value based on a current value.
B12 → Current value of the Year (2022).
C5:C11 → Known range of Y values (Sales).
B5:B11 → Known X value (Years)
- Output: $20971

Press ENTER.
You can see the result in cell C12.

Method 3 – Using the FORECAST.ETS Function with Multiple Variables

Enter the following formula in cell C12.

=FORECAST.ETS(B12,C5:C11,B5:B11)

Formula Breakdown

ETS(B12,C5:C11,B5:B11) → the FORECAST.ETS function determines the future value based on existing historical value.
B12 → Target_date (2022).
C5:C11 → Historical values.
B5:B11 → Timeline (known years).
- Output: $ 21030

Press ENTER.
You can see the result in cell C12.

Method 4 – Using the FORECAST.ETS.CONFINT Function

The following dataset contains the ‘Year’ and ‘Sales’ values. However, the sales for the year 2019 are missing.

We will use FORECAST.ETS function to determine FORECAST.ETS for the year 2019.
Then, we will use the FORECAST.ETS.CONFINT function to determine Confidence Interval.
After that, we will determine Lower Bound and Upper Bound.

Enter the following formula in cell D9.

=FORECAST.ETS(B9, C5:C8, B5:B8)

Press ENTER.
You can see the result in cell D9.

To determine the Confidence Interval, enter the following formula in cell E9.

=FORECAST.ETS.CONFINT(B9,C5:C8,B5:B8)

Formula Breakdown

ETS.CONFINT(B9, C5:C8, B5:B8) → determines the Confidence Interval for a forecast value at a specified target date.
B9 → Target_date.
C5:C8 → Historical values.
B5:B8 → Timeline.
- Output: 288.5851827

Press ENTER.
You can see the Confidence Interval for the Year 2022 in cell E9.

To find out the Lower Bound, enter the following formula in cell F9.

=D9-E9

This subtracts the Confidence Interval from FORECAST.ETS value.

Press ENTER.
You can see the Lower Bound in cell F9.

To determine the Upper Bound, enter the following formula in cell G9.

=D9+E9

This adds the Confidence Interval from FORECAST.ETS value.

Press ENTER.
You can see the Upper Bound in cell G9.

Method 5 – Employing FORECAST.ETS.SEASONALITY Function

Enter the following formula in cell C17.

=FORECAST.ETS.SEASONALITY(C5:C16,B5:B16,1,1)

Formula Breakdown

ETS.SEASONALITY(C5:C16, B5:B16,1,1) → determines the Season Length for specific repetitive time length.
C5:C16 → Historical values.
B5:B16 → Timeline.
1 → Data Completion
1 → Aggregation
- Output: 4

Press ENTER.
The result will display in cell C17.

Method 6 – Using FORECAST.ETS.STAT Function with Multiple Variables

We have 8 statistical argument types:

Alpha (base value): Smoothing value between 0 and 1, controlling data point weighting.
Beta (trend value): Determines trend calculation (higher value gives more weight to recent trends).
Gamma (seasonality value): Controls ETS forecast seasonality (increasing value emphasizes recent seasonal periods).
MASE (mean absolute scaled error): Evaluates forecast accuracy.
SMAPE (symmetric mean absolute percentage error): Measures accuracy based on error proportion.
MAE (mean absolute error): Calculates average forecast error size (independent of direction).
RMSE (root mean square error): Evaluates discrepancies between observed and projected values.
Step size detected: Detected timeline step size.

Next, we will determine the Value of these argument types.

To find the value of Alpha, enter the following formula in cell E5.

=FORECAST.ETS.STAT(C5:C12,B5:B12,1)

Formula Breakdown

ETS.STAT(C5:C12,B5:B12,1) → the FORECAT.ETS.STAT function returns the statistical value.
C5:C12 → Historical values.
B5:B12 → Timeline.
1 → The Statistics_type which is Alpha in that case.
- Output: 0.126