How to Use FORECAST Function with Multiple Variables in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking to use 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.


How to Use FORECAST Function with Multiple Variables in Excel: 6 Examples

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


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


3. Inserting FORECAST.ETS Function with Multiple Variables

In this method, we will use the 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


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 for 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


Download Practice Workbook

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


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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