Excel FORECAST.ETS.STAT function
Stats and forecasting run hand in hand. However, there are numerous function in Microsoft Excel that helps to predict a future value based on the previous data. Wouldn’t it be great if, along with the forecasting, we also get the statistical value related to forecasting? For that purpose, we have Excel have introduced Excel FORECAST.ETS.STAT function.
In this tutorial, we will cover the definition of the FORECAST.ETS.STAT function, its syntax, parameter, error type and its reasons, and examples to comprehend how this formula returns a specified statistical value relating to a time series.
What is Excel FORECAST.ETS.STAT function?
“The FORECAST.ETS.STAT function in Excel returns a specified statistical value relating to a time series exponential smoothing forecasting.“
The Excel FORECAST.ETS.STAT function returns a particular statistical value related to time series forecasting with the FORECAST.ETS function. The statistic_type argument determines which statistic is returned by FORECAST.ETS.STAT. This Function works using the advanced machine learning algorithms, such as Exponential Triple Smoothing (ETS).
Note: The Forecast.Ets.Stat function was added in Microsoft with Excel 2016. Therefore, if you are using an Excel version below Excel 2016, this function will be not available. It is also not available in Excel 2016 for Mac.
Syntax
Parameter
Values (required) – This argument represents an array of historical known values corresponding to an array of dates/times.
Timeline (required)- This parameter represents the independent array of dates/times, equivalent to each of the values.
This must satisfy the following:
- The specified timeline array must carry the exact same length as the given array values;
- The given dates/times in the timeline must fall under a consistent step length between them.
- There may be duplicates in the timeline, whose corresponding values will be aggregated, as defined by the [aggregation] argument.
- The given dates/times in the timeline can be specified in any chronological order.
statistic_type (required)- This parameter represents a numeric value, signifying which statistical value is to be returned.
This integer value must fall between the range 1 and 8, where each value returns the following:
| statistic_type | Statistic Returned |
|---|---|
| 1 | It signifies the Alpha parameter of ETS algorithm |
| 2 | This value signifies the Beta parameter of ETS algorithm |
| 3 | It signifies the Gamma parameter of ETS algorithm |
| 4 | It represents the MASE metric |
| 5 | It represents SMAPE metric |
| 6 | It represents MAE metric |
| 7 | It represents RMSE metric |
| 8 | It represents Step size detected |
Seasonality (optional): This argument indicates the algorithm should be used to detect seasonality in the data.
Since its an optional parameter, it could be omitted as well. But if supplied, this parameter must be a positive integer between 0 and 8784, where the seasonality values indicate the following:
| [seasonality] | Argument |
|---|---|
| 0 | 0 indicates no seasonality value (i.e., use the linear algorithm for the forecast). |
| 1 (or omitted) | If 1 is passed, this function automatically calculates the seasonality value and take positive, whole numbers for the length of the seasonal pattern. |
| integer ≥ 2 & ≤ 8784 | This value use patterns of this length as the seasonality. |
NOTE: A higher seasonality number will result in the #NUM! error.
Data completion (optional) – This parameter represents how the algorithm should handle missing points in the timeline.
Since it is an optional parameter, it could be omitted as well. But if supplied, this parameter argument can have the value 0 or 1 indicating the following:
| [data completion] | Algorithm |
|---|---|
| 0 | Treat missing points as having the value zero. |
| 1 (or omitted) | Calculate the value for missing points to be the average of the neighbouring values. |
Aggregation (optional)- This parameter specifies how the algorithm should aggregate values that have the same timestamp.
Since it is an optional parameter, it could be omitted as well. But if supplied, this parameter argument can be any integer between 1 and 7 indicating the following:
| [aggregation] | Aggregation Method |
|---|---|
| 1 (or omitted) | It represents Average |
| 2 | It represents Count |
| 3 | It represents Counta |
| 4 | It represents Max |
| 5 | It represents Median |
| 6 | It represents Min |
| 7 | It represents Sum |
Return
The FORECAST.ETS.STAF function returns a statistical value as a result of time series forecasting.
Examples
Example 1: Using the FORECAST.ETS.STATS function calculate the statistical value relating to this forecast.
As you can see in the below table, we are given a monthly sales table for 2020, and based on the previous value, we have to forecast the sales figure for Jan 2021 and Feb 2021 (to predict the value use the existing FORECAST.ETS function). But as asked in the question, before predicting the values, we should know the statistical value relating to this forecast.
The Excel FORECAST.ETS.STAT function can be used to return statistical information relating to this forecast. To compute the statistical value related to a forecasting using the Excel FORECAST.ETS.STAT () function follow the below-given steps:
STEP 1: Select the cell
In the above question, we are already given a helper column to calculate the Statistical value. Therefore, we will select the first cell of this column, i.e., F4, to apply the FORECAST.ETS.STAT() formula.
STEP 2: Type the FORECAST.ETS.STAT function
To compute the statistical value for the given forecasted, we will use the inbuilt FORECAST.ETS.STAT() function. Therefore, start typing the function with the equal to (=) symbol followed by the pre-defined FORECAST.ETS.STAT function.
Our formula will become: =FORECAST.ETS.STAT(
STEP 3: Supply the Parameters
- At first, this function will ask you to specify the ‘Value’ parameter. Here, we will specify an array of historical known values corresponding to an array of dates/times. The formula will be =FORECAST.ETS.STAT($C$3:$C$14,
- The next argument is Timeline. This parameter should contain independent array of dates/times. The formula will be =FORECAST.ETS.STAT($C$3:$C$14,$B$3:$B$14
- The next argument we will specify the statistical value for this function. Though the in the question the statistical value is already provided. So in this parameter, we will refer to the cell: =FORECAST.ETS.STAT($C$3:$C$14,$B$3:$B$14,E4)
- The last 3 arguments of these functions are optional. Therefore, we have omitted them. Excel will use the following default value in its place:
- Automatically calculate the seasonality and use positive, whole numbers for the length of the seasonal pattern.
- For the parameter Data completion, 1 will be supplied as default option so as to complete missing points as the average of the neighbouring points.
- In the Aggregation parameter, value 1 will be passed as a default option so it can aggregate multiple data values with the identical timestamp by using the AVERAGE function.
Therefore, the overall formula becomes:
=FORECAST.ETS.STAT($C$3:$C$14,$B$3:$B$14,E4)
NOTE: Since we will copy the formula to other cells, therefore using the absolute cell references (like $C$3:$C$14) we have locked the cells to prevent them from changing their values when copying the formula to other cells.
Step 4: The FORECAST.ETS.STAT function will return the output
As a result, the FORECAST.ETS.STAT function will return a statistical value as a result of time series forecasting.
STEP 5: Drag the formula cell to replicate the formula
Next, we will figure out the statistical values down the cell using the same formula. For the rest of the cells, the Values and Timeline parameter will be the same (therefore, we have used absolute references), only changing the statistical value.
Select the F4 formula cell and move your cursor towards the right corner of the selected box. As a result, the cursor will change into a plus (+) icon. Drag the icon to the following cells, and your surprise formula will be replicated down the cells.
You will have the following Statistical Values for different statistic_type.
Very Well! Now, as we have learned how to use Excel FORECAST.ETS.STAT function, let’s work on another example where this function is not working and throws an error.
Example 2: Compute the statistical value if the statistic_type is not included in the range of 1-8.
In the previous example, if you will notice, all the statistic_type values were given in between the range of 1-8. What if this range exceeds 8? In the below table, some of the values of our statistical_type do not fall in between the range of 1-8. Using the FORECAST.ETS function, let’s figure the output.
The Excel FORECAST.ETS.STAT function returns a #NUM error if the value for statistic_type is not within 1-8. To compute the statistical output for the above table following are the steps:
STEP 1: Select the cell
In the above question, we are already given a helper column to calculate the Statistical value. Therefore, we will select the first cell of this column, i.e., F4, to apply the FORECAST.ETS.STAT() formula.
STEP 2: Type the FORECAST.ETS.STAT function
To compute the statistical value for the given forecasted, we will use the inbuilt FORECAST.ETS.STAT() function. Therefore, start typing the function with the equal to (=) symbol followed by the pre-defined FORECAST.ETS.STAT function.
Our formula will become: =FORECAST.ETS.STAT(
STEP 3: Supply the Parameters
- At first, this function will ask you to specify the ‘Value’ parameter. The formula will be =FORECAST.ETS.STAT($C$3:$C$14,
- The next argument is Timeline. This parameter should contain independent array of dates/times. The formula will be =FORECAST.ETS.STAT($C$3:$C$14,$B$3:$B$14
- The next argument we will specify the statistical_type for this function. Though the in the question the statistical value is already provided. So in this parameter, we will refer to the cell: =FORECAST.ETS.STAT($C$3:$C$14,$B$3:$B$14,E4)
- The last 3 arguments of these functions are omitted.
Therefore, the overall formula becomes:
=FORECAST.ETS.STAT($C$3:$C$14,$B$3:$B$14,E4)
NOTE: Since we will copy the formula to other cells, therefore using the absolute cell references (like $C$3:$C$14) we have locked the cells to prevent them from changing their values when copying the formula to other cells.
Step 4: The FORECAST.ETS.STAT function will return the output
As a result, the FORECAST.ETS.STAT function will return the num! value error because the statistic_type parameter exceeds 8.
Points to Remember for Excel FORECAST.ETS.STAT function
The FORECAST.ETS.STAT function has some key points that one should remember while working:
- ETS.STAT function was introduced in Microsoft with Excel 2016.
- This Function works using the advanced machine learning algorithms, such as Exponential Triple Smoothing (ETS).
- The FORECAST.ETS.STAT function returns a #VALUE! Error, if
- The specified seasonality parameter is not numeric
- The data_completion parameter is not numeric
- The argument aggregation is not numeric
- This function will return #N/A error, if the values and timeline are not the same size
- This function returns a #NUM error, if:
- The Consistent step cannot be found in the timeline parameter
- All given timeline values are the identical
- The numeric value for statistic_type parameter is not within 1-8
- The numeric value for seasonality does not fall under 0-8784
- The numeric value for data_completion is not 0 or 1
- The numeric value for aggregation does not fall within the range of 1-7
