# 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:

### Method 1 – Using the FORECAST Function with Multiple Variables

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

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:

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.

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

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

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

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

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

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

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

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

## Practice Section

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

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

Advanced Excel Exercises with Solutions PDF