FORECAST function - The function returns a value along a linear trend in Excel

FORECAST function: The function performs the calculation or prediction of a future value by using current values ​​using linear regression. In Excel 2016 this function is replaced by FORECAST.LINEAR function. Syntax: FORECAST (x, known_ys, known_xs)

The following article introduces you to the FORECAST function - one of the functions in the statistical function group is very popular in Excel.

FORECAST function - The function returns a value along a linear trend in Excel Picture 1FORECAST function - The function returns a value along a linear trend in Excel Picture 1

Description: The function performs a calculation or prediction of a future value by using current values ​​using linear regression. In Excel 2016 this function is replaced by FORECAST.LINEAR function .

Syntax: FORECAST (x, known_y's, known_x's)

Inside:

- x: Data point to predict a value for it, is a required parameter.

- known_y's: Dependent array of an array or range of data.

- known_x's: Independent array of array or range of data .

Attention:

- If x is not a number -> the function returns the #VALUE! Error value .

- If known_y's or known_x's are empty or contain a different number of data points -> the function returns the # N / A error value

- If known_x's = 0 -> variance, the function returns the DIV / 0 error value !

- FORECAST transform equation is: A + BX where:

(a = bar y - bbar x)

(b = frac {{sum {(x - bar x) (y - bar y)}}}} {{sum {{{(x - bar x)} ^ 2}}}})

And x, y are the average samples of AVERAGE (known_x's) and AVERAGE (known y's).

For example:

Find the predicted value of y and the x value is 25 and 52, respectively:

FORECAST function - The function returns a value along a linear trend in Excel Picture 2FORECAST function - The function returns a value along a linear trend in Excel Picture 2

- Predict the value of y to know the value of x is 25. In the cell to calculate enter the formula : = FORECAST (25, C6: C10, D6: D10)

FORECAST function - The function returns a value along a linear trend in Excel Picture 3FORECAST function - The function returns a value along a linear trend in Excel Picture 3

- Press Enter -> Predict the value of y is:

FORECAST function - The function returns a value along a linear trend in Excel Picture 4FORECAST function - The function returns a value along a linear trend in Excel Picture 4

- Similarly, copy for the case that the value of x is 52 results in:

FORECAST function - The function returns a value along a linear trend in Excel Picture 5FORECAST function - The function returns a value along a linear trend in Excel Picture 5

- Where the number of data points of the two different x and y arrays is different -> the function returns the # N / A error value

FORECAST function - The function returns a value along a linear trend in Excel Picture 6FORECAST function - The function returns a value along a linear trend in Excel Picture 6

Here the value array of x is known only from C6 -> C8 (there are 3 data points) while that of y from C6 -> C10 (there are 5 data points).

Above are instructions and some specific examples when using FORECAST function in Excel.

Good luck!

4 ★ | 1 Vote