- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Statistical - FORECAST Function
Description
The FORECAST function calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing xvalues and y-values, and the new value is predicted by using linear regression.
Syntax
FORECAST(x, known_y's, known_x's)
Arguments
Argument | Description | Required/ Optional |
---|---|---|
X | The data point for which you want to predict a value. | Required |
Known_y's | The dependent array or range of data. | Required |
Known_x's | The independent array or range of data. | Required |
Notes
The equation for FORECAST is a+bx, where −
$$a=\bar{y}-b\bar{x}$$
and
$$b=\frac{\sum \left ( x-\bar{x} \right )\left ( y-\bar{y} \right )}{\sum \left ( x-\bar{x} \right )^2}$$
where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known y's).
If x is nonnumeric, FORECAST returns the #VALUE! error value.
If known_y's and known_x's are empty or contain a different number of data points, FORECAST returns the #N/A error value.
If the variance of known_x's equals zero, then FORECAST returns the #DIV/0! error value.
In Excel 2016, FORECAST Function has been replaced with FORECAST.LINEAR. However, it is still available for backward compatibility.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016