What Is FORECAST.ETS.STAT Excel Function?
The FORECAST.ETS.STAT Excel function is categorized under statistical functions in Excel. The function analyses and forecasts the capabilities for time series data. It is specifically designed for exponential smoothing; this function utilizes the ETS, which stands for Error, Trend, Seasonality, an algorithm to generate accurate predictions based on historical trends
In the following example, we will explore the concept of the FORECAST.ETS.STAT Excel function to achieve the desired outcome. The table shows three values.
To apply the formula, input it in cell D2, as shown below:
=FORECAST.ETS.STAT($B$2:$B$4, $A$2:$A$4,2)
We can see the result in cell D2.
Table of contents
Key Takeaways
- The FORECAST.ETS.STAT function provides output as an array of statistical measures to assess the accuracy and reliability of its forecasts.
- It incorporates statistical model forecasting tools in Excel, making it an indispensable tool for professionals seeking precise and trustworthy forecasts.
- Also, this function utilizes the Exponential Triple Smoothing algorithm, which takes into account trend, seasonality, and error components to generate reliable predictions.
- Excel FORECAST.ETS.STAT function provides additional statistical measures like confidence intervals and prediction intervals, allowing users to evaluate the reliability of their forecasts.
Syntax
Values – This is the required argument. This is an array comprised of a collection of historically known values meticulously aligned with a series of dates and times.
Timeline – This is the required argument. This is the variety of date and time values that align with historical data.
Statistic_type – This is the required argument. This is the numeric value between 1 and 8 to indicate the desired statistic for retrieval. The available options are provided in the table below.
| Statistic Type | Statistic Returned |
|---|---|
| 1 | Alpha parameter of ETS algorithm |
| 2 | Beta parameter of ETS algorithm |
| 3 | Gamma parameter of the ETS algorithm |
| 4 | MASE Metric |
| 5 | SMAPE Metric |
| 6 | MAE Metric |
| 7 | RMSE Metric |
| 8 | Step size detected |
Seasonality – This is the optional argument. This is the numeric value that precisely determines the preferred method for detecting seasonality.
Data_completion – This is the optional argument. This is the numeric value that denotes the preferred method for handling missing data points within the specified range.
| Data Completion | Algorithm |
|---|---|
| 0 | Missing values are treated as zeroes. |
| 1 | Automatic Completion uses linear interpolation. |
Aggregation – This is the optional argument. This is the allocation of a numeric value that assumes utmost significance as it precisely designates the function to be employed for aggregating identical time values.
| Aggregation | Aggregation Method |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MEDIAN |
| 6 | MIN |
| 7 | SUM |
How To Use FORECAST.ETS.STAT Function In Excel? (With Steps)
To utilize the FORECAST.ETS.STAT function in Excel: follow these steps.
1. Access From The Excel Ribbon
- Choose the empty cell which will contain the result. Go to the Formulas tab and click it.
- Select the More Functions option from the menu.
- Select the Statistical option from the drop-down list. Select FORECAST.ETS.STAT from the drop-down menu.
- A window called Function Arguments appears. As the number of arguments, enter the value in the value, timeline, statistical_type, data_completion, and aggregation. Select OK.
2. Enter The Worksheet Manually
Step 1: Select an empty cell for the output. Type =FORECAST.ETS.STAT() in the selected cell. Alternatively, type =F and double-click the FORECAST.ETS.STAT function from the list of suggestions shown by Excel.
Step 2: Press the Enter key to get the result.
Examples
Example #1
In this example, we will delve into the concept of the FORECAST.ETS.STAT Excel function and explore its practical application. Our objective is to calculate the statistical value from 1 to 8.
The data table presents the values for the year 2023, and we will also calculate the statistical value. Let’s prepare an organized table and proceed with the calculation.
To find the output of the FORECAST.ETS.STAT function in cell E2, follow these steps:
Step 1: To start with, select the cell where we will find the FORECAST.ETS.STAT. For this example, we will use cell E2. Enter the following values:
Step 2: The complete FORECAST.ETS.STAT formula is:
=FORECAST.ETS.STAT($B$2:$B$13,$A$2:$A$13,D2)
Step 3: As a result, you will see the corresponding value displayed in cells E2 to E9, similar to the one shown in the provided image.
Example 2
In this example, we will dive deep into the concept of the FORECAST.ETS.STAT Excel function and explore its practical application. Our goal is to calculate the statistical value.
Now, let’s take a look at the data table that presents the values for the year 2022 month from January to September. We will also calculate the statistical value.
To find the output of the FORECAST.ETS.STAT function in the magnificent cell D2, follow these simple steps:
Step 1: To begin with, select the cell where we want to find FORECAST.ETS.STAT. For this example, we will use the cell D2.
Step 2: Now, we will enter the complete FORECAST.ETS.STAT formula to calculate the value we seek.
=FORECAST.ETS.STAT($B$2:$B$10,$A$2:$A$10,4)
Step 3: We can see the result in cell D2, just like the one shown in the provided image.
Example #3
For example, the images below will delve into the concept of the FORECAST.ETS.STAT Excel function and explore how it can be practically applied. Our main objective is to calculate the statistical value.
Now, let’s take a closer look at the data table that presents the values for the year 2021 and year 2022. We’ll also calculate the statistical value.
To calculate the output of the FORECAST.ETS.STAT function in the cell D2, follow these simple steps:
Step 1: Start by selecting the cell where the FORECAST.ETS.STAT will happen. In this case, we’ll use cell D2.
Step 2: Next, we’ll enter the complete FORECAST.ETS.STAT formula to calculate the desired value. The formula looks like this:
=FORECAST.ETS.STAT($B$2:$B$15,$A$2:$A$15,6)
Step 3: We can see the result in cell D2, just like the one shown in the provided image.
By following these steps, you’ll be able to easily find the value using the FORECAST.ETS.STAT function.
Important Things To Note
- The error #N/A occurs when the lengths of the provided values and timeline arrays do not match.
- The error “#NUM!” occurs when a consistent step size cannot be identified in the dates or times of the provided timeline, when the statistic_type value is not within the valid range of 1 to 8, when the seasonality value is not within the valid range of 0 to 8784 and when the data completion is not equal to 0 or 1 and when the aggregation value is not within the valid range of 1 to 7.
- The error #VALUE! occurs when one or more of the arguments, including statistic_type, seasonality, data completion, or aggregation, are not numeric.
Frequently Asked Questions (FAQs)
The FORECAST.ETS.STAT Excel function takes two inputs: a range of historical data and the number of future points to forecast. The historical data range should include numeric values showing time series observations in chronological order without any missing or empty cells. The function then analyses this data to identify underlying trends, seasonality patterns, and irregularities.
Consider the below example.
Select cell E2 and insert the formula:
=FORECAST.ETS.SEASONALITY($B$2:$B$10,$A$2:$A$10,E2)
Press Enter key. We can see the result as shown in the below image.
The #NUM! error occurred because the statistic type value is not in between 1 to 8.
The #VALUE! error occurred because the argument entered is non-numeric.
The #N/A! error occurred because the length of the value does not match the array.
• The function is only available in Microsoft Excel 2016 or later versions. Therefore, if you are using an older version, you will not have access to this powerful forecasting tool.
• The accuracy of the forecasts heavily depends on the quality and quantity of historical data.
• Insufficient or poor-quality data can lead to unreliable predictions. This function uses exponential smoothing techniques, which assume that patterns observed in historical data will continue.
The FORECAST.ETS.STAT function in Excel is different from other forecasting functions due to its ability to utilize advanced statistical models unlike traditional forecasting functions such as FORECAST or TREND, which rely on simpler algorithms like linear regression, the ETS.STAT function incorporates exponential smoothing and state-space modelling techniques. This enables it to capture complex patterns and variability present in time series data more accurately.
Download Template
This article must help us understand the FORECAST.ETS.STAT Excel Function’s formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to FORECAST.ETS.STAT Excel Function in Excel & its meaning. Here we explain how to use the FORECAST.ETS.STAT Excel for cell references, examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply