## Excel FORECAST.ETS.STAT functionStats 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 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.## SyntaxFORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation]) ## Parameter
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.
This integer value must fall between the range 1 and 8, where each value returns the following:
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:
## NOTE: A higher seasonality number will result in the #NUM! error.
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:
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:
## ReturnThe 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:
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.
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:
- 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:
## 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.
As a result, the FORECAST.ETS.STAT function will return a statistical value as a result of time series forecasting.
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:
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.
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:
- 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:
## 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.
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 functionThe 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
Next TopicExcel FORECAST.ETS.SEASONALITY function |