Documentation/Calc Functions/FORECAST
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
FORECAST
Category:
Statistical Analysis
Summary:
Uses least-squares simple linear regression to fit a straight line to known pairs of x-y values. This regression line is then used to calculate an unknown y-value for a specified x-value.
FORECAST can be used to extrapolate future values based on existing x-y data.
Syntax:
FORECAST(Value; Data Y; Data X)
Returns:
Returns a real number, which is the y-value that lies on the regression line for the given x-value.
Arguments:
Value is a real number, or a reference to a cell containing that number, which is the x-value for which the unknown y-value is to be calculated.
Data Y is an array of real numbers containing the known y-values. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array.
Data X is an array of real numbers containing the known x-values. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array. Data X must have the same dimensions as Data Y. There is no limitation on the order of the values in Data X and, in particular, they need not be in ascending order. No error is raised if the same value appears multiple times in Data X, even when each occurrence has a different associated y-value in Data Y.
- If Value is non-numeric, then FORECAST reports a #VALUE! error.
- If either Data Y or Data X is not a multi-cell array, then FORECAST reports a #VALUE! error.
- If the dimensions of Data Y and Data X are not identical, then FORECAST reports an invalid argument error (Err:502).
- If any cell in either array is empty or contains non-numeric data, then that cell is ignored along with the corresponding cell in the other array.
- If all cells in the arrays are ignored, then FORECAST reports a #VALUE! error.
- If Data X has zero variance, then FORECAST reports a #DIV/0! error. This will occur when the two arrays each contain only one numeric x-y pair.
Additional details:
- Calc's FORECAST and FORECAST.LINEAR functions perform identical calculations. The requirements for FORECAST are specified in ODF 1.2; FORECAST.LINEAR is provided for interoperability with Microsoft Excel.
- FORECAST finds the regression line [math]\displaystyle{ y~=~\alpha\:+\:\beta x }[/math] where:
- [math]\displaystyle{ \alpha~=~\bar{y}\:-\:\beta\bar{x} }[/math]
- [math]\displaystyle{ \beta~=~\frac{\sum{(x-\bar{x})(y-\bar{y})}}{\sum(x-\bar{x})^2} }[/math]
- [math]\displaystyle{ \bar{x} }[/math] and [math]\displaystyle{ \bar{y} }[/math] are the means of the x-values and y-values respectively.
- For more information about simple linear regression, visit Wikipedia's Simple linear regression page.
Examples:
The data in the following table is utilized in the final example below.
A | B | C | |
---|---|---|---|
1 | Data X | Data Y | Value |
2 | 4 | 36 | 15 |
3 | 2 | 91 | |
4 | 9 | 25 | |
5 | 10 | 38 | |
6 | 6 | 80 | |
7 | 7 | 64 | |
8 | 1 | 42 | |
9 | 7 | 39 | |
10 | 4 | 63 |
Formula | Description | Returns |
---|---|---|
=FORECAST(10; {4, 6, 8}; {1, 2, 3}) | Here FORECAST calculates the regression line as [math]\displaystyle{ y~=~2x+2 }[/math] and returns the y-value that corresponds to x=10. | 22 |
=FORECAST(170; {8, 9, 10, 11}; {50, 80, 110, 140}) | Here FORECAST calculates the regression line as [math]\displaystyle{ y~=~0.03333x + 6.33333 }[/math] and returns the y-value that corresponds to x=170. | 12 |
=FORECAST(C1; B1:B4; A1:A4) where cells A1:A4 contain the dates 2023-01-01, 2023-02-01, 2023-03-01, and 2023-04-01 respectively; cells B1:B4 contain the numbers 1, 5, 9, and 11 respectively; and cell C1 contains the date 2023-05-01. | Here FORECAST is given data for the first four months of the year and predicts the value expected for the fifth month of the same year. | 15.0434488968933 |
=FORECAST(C2; B2:B10; A2:A10) where the data is as shown in the above table. | Here FORECAST calculates the regression line as [math]\displaystyle{ y = -3.09281x + 70.29341 }[/math] and returns the y-value that corresponds to x=15. | 23.9011976047904 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
FORECAST