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

Posted on by Afia Kona

Method 1 – Using the FORECAST Function

  • 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.

The output of the used Function

Method 2 – Using the FORECAST.LINEAR Function

  • Enter the following formula in cell C12.

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

 Applying FORECAST.LINEAR Function for Multiple Variables in Excel

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.

Result of the used function

Method 3 – Using the FORECAST.ETS Function

  • Enter the following formula in cell C12.
=FORECAST.ETS(B12,C5:C11,B5:B11)

 Inserting FORECAST.ETS Function for Multiple Variables in Excel


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.

The Output of the FORECAST.ETS Function for Multiple Variables

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.
  • We will use the FORECAST.ETS.CONFINT function to determine Confidence Interval.
  • We will determine Lower Bound and Upper Bound.

Dataset for FORECAST.ETS.CONFINT Function

  • Enter the following formula in cell D9.
=FORECAST.ETS(B9, C5:C8, B5:B8)

 Applying FORECAST.ETS Function for the Year 2019

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

The result of the FORECAST.ETS Function for Multiple Variables

  • To determine the Confidence Interval, enter the following formula in cell E9.
=FORECAST.ETS.CONFINT(B9,C5:C8,B5:B8)

Inserting FORECAST.ETS.CONFINT Function for Multiple Variables in Excel

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.

Result of the Confidence Interval

  • To find out the Lower Bound, enter the following formula in cell F9.
=D9-E9

This subtracts the Confidence Interval from FORECAST.ETS value.

Subtracting Confidence Interval from Forecast.ETS Value

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

Result of Lower Bound

  • To determine the Upper Bound, enter the following formula in cell G9.
=D9+E9

This adds the Confidence Interval from FORECAST.ETS value.

Adding Confidence Interval with Forecast.ETS Value

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

Result of the Upper Bound

Method 5 – Using the FORECAST.ETS.SEASONALITY Function

  • Enter the following formula in cell C17.
=FORECAST.ETS.SEASONALITY(C5:C16,B5:B16,1,1)

 Inserting FORECAST.ETS.SEASONALITY Function for Multiple Variables

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.

The output of the Season Lenght

Method 6 – Using FORECAST.ETS.STAT Function

We have 8 statistical argument types:

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

We will determine the Value of these argument types.

Dataset for the FORECAST.ETS.STAT Function

  • To find the value of Alpha, enter the following formula in cell E5.
=FORECAST.ETS.STAT(C5:C12,B5:B12,1)

Inserting FORECAST.ETS.STAT for Multiple Variables in Excel

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
  • Press ENTER.
  • You can see the output in cell E5.

The result of the Alpha Value

  • To determine the Beta value, enter the following formula in cell E6.
=FORECAST.ETS.STAT(C5:C12, B5:B12,2)
  • Press ENTER.
  • You can see the result in cell E6.

Inserting FORECAST.ETS.STAT Function for Beta Value

  • To calculate the Gamma value, enter the following formula in cell E7.
=FORECAST.ETS.STAT(C5:C12, B5:B12,3)
  • Press ENTER.
  • You can see the result in cell E7.

Using FORECAST.ETS.STAT Function for Gamma Value

  • To find out the MASE value, enter the following formula in cell E8.
=FORECAST.ETS.STAT(C5:C12, B5:B12,4)
  • Press ENTER.
  • You can see the result in cell E8.

Using FORECAST.ETS.STAT Function for MASE Value

  • To determine the SMAPE value, enter the following formula in cell E9.
=FORECAST.ETS.STAT(C5:C12, B5:B12,5)
  • Press ENTER.
  • You can see the result in cell E9.

Using FORECAST.ETS.STAT Function for SMAPE Value

  • To calculate the MAE value, enter the following formula in cell E10.

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

  • Press ENTER.
  • You can see the result in cell E10.

Using FORECAST.ETS.STAT Function for MAE Value

  • To find the RMSE value, enter the following formula in cell E11.
=FORECAST.ETS.STAT(C5:C12, B5:B12,7)
  • Press ENTER.
  • You can see the result in cell E11.

Using FORECAST.ETS.STAT Function for RMSE Value

  • To determine the Step Size value, enter the following formula in cell E12.

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

  • Press ENTER.
  • You can see the result in cell E12.
  • The Value column is complete.

Using FORECAST.ETS.STAT Function for Step Size Value

Practice Section

You can download the above Excel file and practice the explained methods.

Practice Section for FORECAST Function

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

<< Go Back to Excel FORECAST Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF