How to Use FORECAST Function with Multiple Variables in Excel

If you are looking for using the FORECAST function with multiple variables in Excel, then you have come to the right place. Here, we will walk you through 6 easy examples to do the task smoothly.


Download Practice Workbook

You can download the Excel file and practice while reading this article.


6 Examples of Using FORECAST Function with Multiple Variables in Excel

In the following dataset, you can see the Year and corresponding Sales. Here, we will have to determine the Sales for the year 2022. To do so, we will use the FORECAST function with multiple variables. Here, we used Excel 365. You can use any available Excel version.

Sample dataset for using FORECAST Function


1. Using FORECAST Function with Multiple Variables in Excel

In this method, we will use the FORECAST function with multiple variables to determine the Sales for the year 2022.

Steps:

  • First of all, we will type 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 → is the current value of the Year.
  • C5:C11 → is the known range of Y values. In this case, these are the known Sales.
  • B5:B11 → is the known X value. In this case, these are the known Years.
  • FORECAST(B12,C5:C11,B5:B11) → becomes
    • Output: $20971
  •  At this point, press ENTER.
  • Therefore, you can see the result in cell C12.

The output of the used Function

Read More: TREND vs FORECAST Function in Excel (3 Handy Examples)


2. Applying FORECAST.LINEAR Function

In this method, we will use the FORECAST.LINEAR function with multiple variables to determine the Sales for the year 2022.

Steps:

  • First, we will type 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 → is the current value of the Year.
  • C5:C11 → is the known range of Y values. In this case, these are the known Sales.
  • B5:B11 → is the known X value. In this case, these are the known Years.
  • LINEAR(B12, C5:C11, B5:B11) → becomes
    • Output: $20971
  •  At this point, press ENTER.
  • Hence, you can see the result in cell C12.

Result of the used function

Read More: How to Use VBA Forecast Function in Excel (3 Ideal Examples)


3. Inserting FORECAST.ETS Function with Multiple Variables

In this method, we will use FORECAST.ETS function with multiple variables to determine Sales for the year 2022.

Steps:

  • First, we will type 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 → is the target_date.
  • C5:C11 → are the historical values.
  • B5:B11 → is the timeline or known years.
  • ETS(B12, C5:C11, B5:B11) → becomes
    • Output: $ 21030
  • At this point, press ENTER.
  • Hence, you can see the result in cell C12.

The Output of the FORECAST.ETS Function for Multiple Variables

Read More: [Fixed!] FORECAST Function Not Accurate in Excel (5 Solutions)


4. Use of FORECAST.ETS.CONFINT Function

In the following dataset, you can see 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

Steps:

  • In the beginning, find out the FORECAST.ETS value, we will type the following formula in cell D9.
=FORECAST.ETS(B9, C5:C8, B5:B8)

 Applying FORECAST.ETS Function for the Year 2019

  • At the moment, press ENTER.
  • As a result, you can see the result in cell D9.

The result of the FORECAST.ETS Function for Multiple Variables

  • Furthermore, to find out the Confidence Interval, we will type 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 is the target_date.
  • C5:C8 → are the historical values.
  • B5:B8 → is the timeline.
  • ETS.CONFINT(B9, C5:C8, B5:B8) becomes
    • Output: 288.5851827
  • Then, press ENTER.
  • As a result, you can see the Confidence Interval for the Year 2022 in cell E9.

Result of the Confidence Interval

  • Moreover, to find out the Lower Bound, we will type the following formula in cell F9.
=D9-E9
  • This simply subtracts the Confidence Interval from FORECAST.ETS value.

Subtracting Confidence Interval from Forecast.ETS Value

  • At this point, press ENTER.
  • As a result, you can see the Lower Bound in cell F9.

Result of Lower Bound

  • Moreover, to find out the Upper Bound, we will type the following formula in cell G9.
=D9+E9
  • This simply adds the Confidence Interval from FORECAST.ETS value.

Adding Confidence Interval with Forecast.ETS Value

  • Afterward, press ENTER.
  • Therefore, you can see the Upper Bound in cell G9.

Result of the Upper Bound


5. Employing FORECAST.ETS.SEASONALITY Function

In this method, we will use FORECAST.ETS.SEASONALITY function with multiple variables to determine the Season Length.

Steps:

  • In the beginning, we will type 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 foe specific repetitive time length.
  • C5:C16 → are the historical values.
  • B5:B16 → is the timeline.
  • 1 → is the Data Completion
  • 1 → is the Aggregation
  • ETS.SEASONALITY(C5:C16, B5:B16,1,1) → becomes
    • Output: 4
  •  After that, press ENTER.
  • Hence, you can see the result in cell C17.

The output of the Season Lenght


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

In this method, we will use FORECAST.ETS.STAT function for multiple variables to determine the statistical type values.

We have 8 statistical argument types. These are:

  • Alpha (base value) – is a smoothing value between 0 and 1, which controls how the data points are weighted. A higher value indicates that recent data is weighted more heavily.
  • The beta (trend value) – values between 0 and 1 determine the trend calculation. Recent trends are given more weight when the value is higher.
  • Gamma (seasonality value) – A value between 0 and 1 that controls ETS forecast seasonality. An increasing value indicates that the recent seasonal period should be given more weight.
  • MASE (mean absolute scaled error) – a way to evaluate how accurate a forecast was.
  • SMAPE (symmetric mean absolute percentage error) – an evaluation of accuracy based on the proportion or magnitude of errors.
  • MAE (mean absolute error) – independent of their direction, calculates the average size of the forecast errors.
  • RMSE (root mean square error) – an evaluation of the discrepancies between the observed and projected values.
  • Step size detected – the timeline’s detected step size.

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

Dataset for the FORECAST.ETS.STAT Function

Steps:

  • First of all, to find out the value of Alpha, we will type 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 → are the historical values.
  • B5:B12 → indicate the timeline.
  • 1 → indicates the Statistics_type which is Alpha in that case.
  • ETS.STAT(C5:C12, B5:B12,1) → becomes
    • Output: 0.126
  •  Then, press ENTER.
  • As a result, you can see the output in cell E5.

The result of the Alpha Value

  • Furthermore, to find out the Beta value, we type the following formula in cell E6.
=FORECAST.ETS.STAT(C5:C12, B5:B12,2)
  •  Then, press ENTER.
  • Therefore, you can see the result in cell E6.

Inserting FORECAST.ETS.STAT Function for Beta Value

  • In addition, to find out the Gamma value, we type the following formula in cell E7.
=FORECAST.ETS.STAT(C5:C12, B5:B12,3)
  • Then, press ENTER.
  • Therefore, you can see the result in cell E7.

Using FORECAST.ETS.STAT Function for Gamma Value

  • Along with that, to find out the MASE value, we type the following formula in cell E8.
=FORECAST.ETS.STAT(C5:C12, B5:B12,4)
  • Then, press ENTER.
  • As a result, you can see the result in cell E8.

Using FORECAST.ETS.STAT Function for MASE Value

  • Moreover, to find out the SMAPE value, we type the following formula in cell E9.
=FORECAST.ETS.STAT(C5:C12, B5:B12,5)
  • Then, press ENTER.
  • As a result, you can see the result in cell E9.

Using FORECAST.ETS.STAT Function for SMAPE Value

  • Afterward, to find out the MAE value, we type the following formula in cell E10.

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

  • After that, press ENTER.
  • As a result, you can see the result in cell E10.

Using FORECAST.ETS.STAT Function for MAE Value

  • After that, to find out the RMSE value, we type the following formula in cell E11.
=FORECAST.ETS.STAT(C5:C12, B5:B12,7)
  • Then, press ENTER.
  • Therefore, you can see the result in cell E11.

Using FORECAST.ETS.STAT Function for RMSE Value

  • Finally, to find out the Step Size value, we type the following formula in cell E12.

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

  • Furthermore, press ENTER.
  • Therefore, you can see the result in cell E12.
  • Also, you can see the complete Value column.

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


Conclusion

Here, we show you 6 examples of using the FORECAST function with multiple variables in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.


Related Article

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo