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.

The output of the used Function


Method 2 – Applying 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 with Multiple Variables

  • 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.
  • B12Target_date (2022).
  • C5:C11 → Historical values.
  • B5:B11Timeline (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.
  • Then, we will use the FORECAST.ETS.CONFINT function to determine Confidence Interval.
  • After that, 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 – Employing 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:B16Timeline.
  • 1Data Completion
  • 1Aggregation
    • Output: 4
  • Press ENTER.
  • The result will display in cell C17.

The output of the Season Lenght


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

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.

Next, 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!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo