Excel FORECAST and related functions with formula examples (2023)

This tutorial shows how to use Excel FORECAST and other related functions with formula examples.

There are several functions in Microsoft Excel that help you create linear and exponential smoothing forecasts based on historical data for sales, budgets, cash flow, stock prices, and more.

The focus of this tutorial will be on the two main forecasting functions, but we will also briefly introduce the other functions to help you understand their purpose and basic use.

Excel forecast function

There are six different forecasting functions in the latest versions of Excel.

These two functions worklinearforecast:

FORECAST - Predicts future values using linear regression. backward compatible with legacy features from Excel 2013 and earlier versions.
LINEAR - same as FORECAST function. part of a series of new forecasting features in Excel 2016 and Excel 2019.

The four functions of ETS are designed toexponential smoothingforecast. These features are only available in Excel for Office 365, Excel 2019, and Excel 2016.

ETS - Future price prediction based on exponential smoothing algorithm.
ETS.CONFINT - Calculation of confidence intervals.
ETS.SEASONALITY - Calculate the duration of seasonality or other repeating patterns.
ETS.STAT - returns time series forecast statistics.

Excel forecast function

The FORECAST function in Excel is forLinear regression.In other words, FORECAST predicts future values along the line of best fit based on historical data.

The syntax of the prediction function is as follows:

predict(x, poznato_y, poznato_x)

Where:

X(required) - The numerical value of x for which you want to predict new values of y.
well known(required) - A set of known relative y values.
known_x(required) - A set of known independent x values.

The FORECAST function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.

notes.In Excel 2016 and 2019, this function has been replaced bypredict.linear, but is still available for backwards compatibility.

Excel function FORECAST.LINEAR

The FORECAST.LINEAR function is a modern version of the FORECAST function. It has the same purpose and structure:

FORECAST.LINEAR(x, known_y, known_x)

This feature is available in Excel for Office 365, Excel 2019, and Excel 2016.

How they calculate FORECAST and FORECAST.LINEAR future values

Both functions calculate future y values using a linear regression equation:

y = a + bx

WhereONEThe constant (intercept) is:

The factor b (slope of the line) is:

The x̄ and ş values are the sample mean (average) of the known x and y values.

Excel's forecast function does not work:

If your FORECAST formula throws an error, it's probably because:

Error #N/A! displayed if known_x and known_y have different span lengths or are empty.
If the value x is not a number, the formula returns a #VALUE! error.
#DIV/0!Error if known_x has zero variance.

How to use the FORECAST function in Excel - formula example

As mentioned earlier, the Excel functions FORECAST and FORECAST.LINEAR are used to forecast a linear trend. They work best with linear data sets and when you want to predict overall trends while ignoring trivial fluctuations in the data.

For example, we will try to predict website traffic for the next 7 days based on data from the previous 3 weeks.

Using the known y values (number of visitors) from B2:B22 and the known x values (dates) from A2:A22, the prediction formula is as follows.

Excel 2019 - Excel 2000:

=prognoza(A23, $B$2:$B$22, $A$2:$A$22)

Excel 2016 and Excel 2019:

=FORECAST.LINEAR(A23, $B$2:$B$22, $A$2:$A$22)

where A23 is the new value of x for which you want to predict future values of y.

Depending on your version of Excel, enter one of the above formulas in any blank cell in row 23, copy it to as many cells as you need, and you'll get:

Note that we are locking the rangeabsolute cell reference(such as $A$2:$A$2) to prevent changing formulas when copying to other cells.

Plotted on a graph, our linear forecast looks like this:

Detailed steps for creating such a graph are found atLinear regression prediction graph.

If you wantrepeating patternView historical data and use FORECAST.ETS instead of Excel's FORECAST function. The next part of our guide will show you how to do this.

Excel function FORECAST.ETS

The FORECAST.ETS function is used toexponential smoothingPrediction based on a range of existing values.

Specifically, it is based on the AAA versionexponential triple smoothingalgorithm (ETS), hence the name of the function. The algorithm removes trivial deviations from data trends by detecting seasonal patterns and confidence intervals. "AAA" stands for additive error, additive trend and additive seasonality.

The FORECAST.ETS function is available in Excel for Office 365, Excel 2019, and Excel 2016.

The Excel FORECAST.ETS syntax is as follows:

FORECAST.ETS(target_date, values, schedule, [season], [data_completion], [aggregation])

Where:

target date(required) - Data point for predicted value. It can be expressed as a date/time or as a number.
values(required) - A row or series of historical data whose future values you want to predict.
Timetable(required) - An array of date/time or independent numeric data with a constant step between them.
seasonal(optional) - Number representing the duration of the seasonal sample:
- 1 or omitted (default) - Excel automatically detects seasonal changes using positive integers.
- 0 - No seasonality, i.e. linear forecast.
The maximum seasonality allowed is 8,760, which is the number of hours in a year. Higher seasonal numbers will result in #NUM! error.
data completion(Optional) - Account for missing points.
- 1 or omitted (default) - fill in missing points by averaging neighboring points (linear imputation).
- 0 - Treat missing points as zero.
polymerization(Optional) - Specifies how to aggregate multiple data values with the same timestamp.
- 1 or omitted (default) - The AVERAGE function is used for summation.
- Your other options are: 2 - COUNTA, 3 - COUNTA, 4 - MAX, 5 - MEDIAN, 6 - MIN, and 7 - SUM.

5 things to know about FORECAST.ETS

For the FORECAST.ETS function to work correctly, the time axis should have afixed interval- Hourly, daily, monthly, quarterly, yearly, etc.
This function is best suited for nonlinear data sets with seasonal or other characteristicsrepeating pattern.
kada excelI can't spot the pattern, the function reverts to linear prediction.
This function can be used withincomplete data setUp to 30% of data points are missing. Here are the missing pointsdata completiondiscussion.
Although a program with fixed steps is needed, there may be onerepeatin a date/time range. Values with the same timestamp are sorted bypolymerizationdiscussion.

The FORECAST.ETS function does not work:

If your type produces an error, it could be one of the following:

If #N/A appearsvaluesITimetableThe strings have different lengths.
this#value! errorif he returnsseasonal,data completionthepolymerizationThe argument is not numeric.
this#NUM! errorIt can be rejected for any of the following reasons:
- Unable to detect fixed step sizeTimetable.
- thisseasonalThe value is outside the supported range (0 - 8.7600).
- thisdata completionThe value is not 0 or 1.
- thispolymerizationThe value is outside the valid range (1 - 7).

How to use the FORECAST.ETS function in Excel - type example

To see how future values calculated using exponential smoothing differ from linear regression forecasts, let's create the FORECAST.ETS formula for the same data set used in the previous example:

=FORECAST.ETS(A23, $B$2:$B$22, $A$2:$A$22)

Where:

A23 is the target date
$B$2:$B$22 is historical data (values)
$A$2:$A$22 is the date (Timetable)

Omitting the last three arguments (seasonal,data completionthepolymerization) we rely on Excel's default settings. Excel perfectly predicts the trend:

Excel function FORECAST.ETS.CONFINT

The FORECAST.ETS.CONFINT function is used to calculate confidence intervals for forecast values.

The confidence interval is a measure of the accuracy of the prediction. The smaller the interval, the more confident you can be in your prediction for a particular data point.

FORECAST.ETS.CONFINT is available in Excel for Office 365, Excel 2019, and Excel 2016.

This function has the following parameters:

FORECAST.ETS.CONFINT(target_date, values, schedule, [confidence_level], [seasonality], [data-completion], [concentration])

As you can see, the syntax of FORECAST.ETS.CONFINT is the same asPrognosis.ETSfunction, except for this additional parameter:

Level of trust(optional) - A number between 0 and 1 that specifies the confidence level for calculating the interval. It is usually given as a decimal number, but percentages can also be used. For example, to set a confidence level of 90%, you can enter 0.9 or 90%.

If omitted, the default value of 95% is used, meaning that forecast data points are expected to fall within a radius compared to the value returned by FORECAST.ETS 95% of the time.
If the confidence is outside the supported range (0 - 1), the formula returns a #NUM! error.

Type Example FORECAST.ETS.CONFINT

To see how this works in practice, let's calculate a confidence interval for a sample data set:

=FORECAST.ETS.CONFINT(A23,$B$2：$B$22,$A$2：$A$22)

Where:

A23 is the target date
$B$2:$B$22 are historical data
$A$2:$A$22 is the date

Omit the last 4 arguments to tell Excel to use the default options:

Set the confidence level to 95%.
Automatic seasonality detection.
Complete missing points are taken as the average of adjacent points.
Use the AVERAGE function to aggregate multiple data values with the same timestamp.

To see what the return values actually mean, see the screenshot below (some rows containing history data are hidden for spacing reasons).

The formula in D23 gives a result of 6441.22 (rounded to two decimal places). This means that 95% of the time the forecast for March 11th is expected to be within the 6441.22 range of the 61075 predicted price (C3). That is 61 075 ± 6441.22.

To find out where the predicted value is likely to fall, you can calculate the limits of the confidence interval for each data point.

I getMinimum limit, to subtract the confidence interval from the predicted value:

=C23-D23

I getupper limit, to add confidence intervals to the predicted values:

=C23+D23

where C23 is the predicted value returned by FORECAST.ETS and D23 is the confidence interval returned by FORECAST.ETS.CONFINT.

Copy the formula above, plot the result on a graph, and you will clearly see the predicted value and confidence interval:

advice.To automatically create such a chart for you, use itExcel Forecast Tablefeature.

Funkcija Excel FORECAST.ETS.SEASONALITY

The FORECAST.ETS.SEASONALITY function is used to calculate the length of a repeating pattern on a given timeline. It is closely related to FORECAST.ETS because both functions use the same seasonality detection algorithm.

This feature is available in Excel for Office 365, Excel 2019, and Excel 2016.

The syntax for FORECAST.ETS.SEASONALITY is:

FORECAST.ETS.SEASONALITY(value, schedule, [data_completion], [aggregation])

For our data set, the formula has the following form:

=FORECAST.ETS.SEASONALITY(B2:B22, A2:A22)

and returns a seasonality of 7, which exactly matches the weekly pattern of our historical data:

Excel function FORECAST.ETS.STAT

The FORECAST.ETS.STAT function returns the specified statistical value associated with an exponentially smoothed time series forecast.

Like other ETS features, it is available in Excel for Office 365, Excel 2019, and Excel 2016.

This function has the following syntax:

FORECAST.ETS.STAT(value, schedule, stat_type, [season], [data_completion], [aggregation])

thisstatistics typeThe parameter indicates which statistic value should be returned:

ONE(base value) - A smooth value between 0 and 1 that controls the weight of the data points. Higher values give more weight to recent data.
Beta(TrendValue) - A value between 0 and 1 that specifies the trend calculation. The higher the value, the more weight is given to the recent trend.
range(SeasonalValue) - A value between 0 and 1 that controls the seasonality of the ETS forecast. The higher the value, the more weight is given to the most recent seasonal cycle.
MASE(Mean Absolute Scale Error) - measure of prediction accuracy.
SMAPE(Symmetrical Mean Absolute Error Rate) - A measure of accuracy based on percentage or relative error.
plum(Mean Absolute Error) - Measures the average size of the prediction errors, regardless of their direction.
root mean square error(root mean square error) - A measure of the difference between predicted and observed values.
detected step- Timeline step size detected.

For example, to return the Alpha parameter for a sample data, we use the following formula:

=prognoza.ETS.STAT(B2:B22, A2:A22, 1)

The following screenshot shows the formulas for other statistics:

This is how time series forecasting is done in Excel. To study all the formulas mentioned in this guide, feel free to download oursSample Excel Forecasting Workbook.Thank you for reading and we hope to see you on our blog next week!

Maybe you're right. . . I'm interested in

Forecasting in Excel: Linear and Exponential Smoothing Forecasting Models
Trend analysis methods, such as the TREND function in Excel
Linear Regression Analysis in Excel
How to Add a Trendline in Excel