Excel offers many useful features and functions that reduce our workload significantly. For example, Excel can predict future output based on past results. We can do this using the FORECAST function in Excel. This function recognizes a pattern of input-output relation and predicts output using it. In Excel, there are different forecasting functions to predict future results based on existing values.
In this article, we are going to learn about the FORECAST function in Excel as well as other forecasting functions.
We’ll also link 4 important articles related to the FORECAST function so that you can get an all-around knowledge about the FORECAST function. This includes the FORECAST function with multiple variables.
Again, you will find a comparative discussion between the TREND and the FORECAST function. This will provide you an insight regarding when to use which function. You can also use the FORECAST function in VBA. This helps to automate tasks and reduce manual interaction to a great level. Moreover, you’ll get a fix for the FORECAST function not being accurate.
So, to bring your Excel skill to the next level, dive through this article and explore the power of the FORECAST function.
What is FORECAST Function in Excel?
- Description
The FORECAST function is a Statistical function in Excel. It calculates or predicts a future value based on existing value. The existing values are known as x-values and y-values and the future value is predicted by using linear regression. For instance, you can predict future numeric values of sales, earnings and expenses, inventory, consumer trends, measurements, etc.
- Purpose
To predict or calculate a future value with a linear trend
- Syntax
=FORECAST(x, known_ys, known_xs)
- Arguments Description
| Value | Required/Optional | Description |
|---|---|---|
| x | Required | The value for which the future value to predict or calculate |
| known_ys | Required | The dependent array or range of data (y values) |
| known_xs | Required | The independent array or range of data (x values) |
- Return Value
A predicted or calculated value
FORECAST Functions in Excel: 5 Examples
In this section, you will learn the FORECAST.LINEAR, the FORECAST.ETS, the FORECAST.ETS.CONFINT, the FORECAST.ETS.SEASONALITY and the FORECAST.ETS.STAT function in Excel.
1. FORECAST.LINEAR Function in Excel
FORECAST.LINEAR is formerly known as the FORECAST function in Excel. Microsoft replaced the FORECAST function with the FORECAST.LINEAR in 2016.
- Purpose
This function predicts the future value based on the existing set of values.
- Equation
y = a + bx
Where,
a = constant value, intercept, which follows,
And b = coefficient, the slope of the line, which follows,
Here,
means, the Average value (arithmetic mean) of the sample value.
- Return Value
A calculated future value
Based on the above discussion, the FORECAST.LINEAR formula for our given dataset will be,
=FORECAST.LINEAR(B18,$C$5:$C$16,$B$5:$B$16)Where,
B18 = The value for which the future value to predict or calculate
$C$5:$C$16 = The dependent array or range of data (y values)
$B$5:$B$16 = The independent array or range of data (x values)
2. FORECAST.ETS in Excel
The FORECAST.ETS function is used to calculate or predict future value based on existing values by using the AAA version of the Exponential Smoothing (ETS) algorithm.
Here,
AAA = Additive Error, Additive Trend, and Additive Seasonality.
ETS = Exponential Triple Smoothing algorithm.
This algorithm loosens up the insignificant deviations in data trends by detecting seasonality patterns and confidence intervals.
- Syntax
=FORECAST.ETS (target_date, values, timeline, [seasonality], [data_completion], [aggregation])
- Argument Description
| Value | Required/Optional | Description |
|---|---|---|
| target_date | Required | The timeline for the prediction should be calculated |
| values | Required | Existing or historical value(y-values), dependent array, or range of data from which a prediction will be calculated. |
| timeline | Required | Numeric independent array or range of values (x-values) |
| [seasonality] | Optional |
Seasonality calculation.
|
| [data_completion] | Optional |
Missing data calculation.
|
| [aggregation] | Optional | Indicates which method to use. Default value is 0 = Average |
- Return Value
A calculation of the predicted value.
Based on the discussion, the FORECAST.ETS formula for our given dataset shown above will be,
=FORECAST.LINEAR(B18,$C$5:$C$16,$B$5:$B$16)Where,
B18 = The value for which the future value to predict or calculate
$C$5:$C$16 = The dependent array or range of data (y values)
$B$5:$B$16 = The independent array or range of data (x values)
3. FORECAST.ETS.CONFINT
The FORECAST.ETS.CONFINT function returns a confidence interval (CI) for a forecast value at a specified timeline. A confidence level of 90% means the predicted values are expected to fall within this radius from the result of the FORECAST.ETS function produced.
- Syntax
=FORECAST.ETS.CONFINT (target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])
- Argument Description
| Value | Required/Optional | Description |
|---|---|---|
| target_date | Required | The timeline for the prediction should be calculated |
| values | Required | Existing or historical value(y-values), dependent array, or range of data from which a prediction will be calculated. |
| timeline | Required | Numeric independent array or range of values (x-values) |
| [confidence_level] | Optional | The confidence level for the calculated confidence interval. A numeric value between 0 and 1 (exclusive). Default 0.95 or 95% |
| [seasonality] | Optional | Seasonality calculation.
|
| [data_completion] | Optional | Missing data calculation.
|
| [aggregation] | Optional | Indicates which method to use. Default value is 0 = Average |
- Return Value
Confidence Interval (CI) value
Based on the above discussion, the FORECAST.ETS.CONFINT formula for our given dataset will be,
=FORECAST.ETS.CONFINT(E5,$C$5:$C$16,$B$5:$B$16,G5)Where,
E5 = The value for which the future value to predict or calculate
$C$5:$C$16 = The dependent array or range of data (y values)
$B$5:$B$16 = The independent array or range of data (x values)
G5 = Confidence level
4. FORECAST.ETS.SEASONALITY
The FORECAST.ETS.SEASONALITY function is used to return the length of a repetitive pattern in a specified timeline.
- Syntax
=FORECAST.ETS.SEASONALITY (values, timeline, [data_completion], [aggregation])
- Argument Description
| Value | Required/Optional | Description |
|---|---|---|
| values | Required | Existing or historical value(y-values), dependent array, or range of data from which a prediction will be calculated. |
| timeline | Required | Numeric independent array or range of values (x-values) |
| [data_completion] | Optional | Missing data calculation.
|
| [aggregation] | Optional | Indicates which method to use. Default value is 0 = Average |
- Return Value
Season length in a specified timeline
Based on the above discussion, the FORECAST.ETS.SEASONALITY formula for our given dataset shown above will be,
=FORECAST.ETS.SEASONALITY($C$5:$C$16,$B$5:$B$16)Where,
$C$5:$C$16 = The dependent array or range of data (Score column as y values)
$B$5:$B$16 = The independent array or range of data (ID column as x values)
5. FORECAST.ETS.STAT in Excel
The FORECAST.ETS.STAT function returns a statistical value relating to the time series forecasting with the FORECAST.ETS function.
Syntax
=FORECAST.ETS.STAT (values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])
- Argument Description
| Value | Required/Optional | Description |
|---|---|---|
| values | Required | Existing or historical value(y-values), dependent array, or range of data from which a prediction will be calculated. |
| timeline | Required | Numeric independent array or range of values (x-values) |
| statistic_type | Required | The type of statistical value to return. The table below shows the 8 possible types and their description,
|
| [seasonality] | Optional | Seasonality calculation.
|
| [data_completion] | Optional | Missing data calculation.
|
| [aggregation] | Optional | Indicates which method to use. Default value is 0 = Average |
- Return Value
A statistical result
The formula for the FORECAST.ETS.STAT function with different statistic types is shown in the picture below,
The formula,
=FORECAST.ETS.SEASONALITY($C$5:$C$16,$B$5:$B$16,1)Where,
$C$5:$C$16 = The dependent array or range of data (Score column as y values)
$B$5:$B$16 = The independent array or range of data (ID column as x values)
1 = Alpha statistic type (this numeric argument can be anything from 1 to 8 based on the requirement)
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
In this article, we’ve discussed the details of the FORECAST function in Excel. We have also linked some additional articles to provide you with everything about the function on a single page. We hope, with a little bit of practice, you can apply the FORECAST function yourself. If you face any issues regarding this, please let us know in the comment section. Team Exceldemy will be there to help you out. Have a good day!
