Documentation/Calc Functions/FORECAST

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

FORECAST

Category:

Statistical Analysis

Summary:

Extrapolates future values based on existing x and y values.

Syntax:

FORECAST(Value; DataY; DataX)

Returns:

Returns a real number which is the predicted value of y using linear regression for the given Value and the 2 data sets, DataY and DataX.

Arguments:

Value is a real number or a reference to the cell containing that number which is the x value, for which the y value on the linear regression is to be returned.

DataY is the array or a reference to the range of known y values.

DataX is the array or a reference to the range of known x values.

  • If DataX has variance of 0 then the function returns a division(#DIV/0!) error.
  • If dimensions of DataX and DataY are not same then the function returns an error value.
  • If either DataX or DataY is an empty array then the function returns an error value and if either of them is a reference to a range of empty cells then the function returns a #VALUE! error.

Additional details:

  • The equation for FORECAST is

y = α + βx,

where α = ӯ - βx̄ and β is given as:

Forecast n.png

Here x̄ and ӯ are mean values for DataX and DataY, respectively.

  • FORECAST and FORECAST.LINEAR performs the same operation.

Examples:

DataY DataX
1 195 200
2 151 180
3 148 178
4 189 165
5 183 192
6 154 144
Formula Description Returns
=FORECAST(150;A1:A6;B1:B6) returns the x value expected for the x value of 150 if the DataX (B1:B6) and DataY (A1:A6) values in both references are linked by a linear trend. 156.839639188173
=FORECAST(170;{8,9,10,11};{50,80,110,140}) returns the y value expected for the x value of 170 if the DataX = {50,80,110,140} and DataY = {8,9,10,11} values in both arrays are linked by a linear trend. 12

Related LibreOffice functions:

FORECAST.ETS.ADD

FORECAST.ETS.MULT

FORECAST.ETS.PI.ADD

FORECAST.ETS.PI.MULT

FORECAST.ETS.SEASONALITY

FORECAST.ETS.STAT.ADD

FORECAST.ETS.STAT.MULT

FORECAST.LINEAR

ODF standard:

Section 6.18.28, part 2

Equivalent Excel functions:

FORECAST