The FORECAST Function - Returns a value along a linear trend in Excel

This article introduces the FORECAST function, one of the statistical functions widely used in Excel.

Description: This function calculates or predicts a future value using current values through linear regression. In Excel 2016, it's replaced by the FORECAST.LINEAR function.

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

In which:

- x: The data point to predict a value for, it's a required parameter.

- known_y's: The dependent array or range of data.

- known_x's: The independent array or range of data.

Note:

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

- If known_y's or known_x's is empty or contains different number of data points -> the function returns an error value #N/A

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

- The transformation equation for FORECAST is: A+BX where:

a=¯y−b¯x$a=\overline{y}-b\overline{x}$

b=∑(x−¯x)(y−¯y)$b=\frac{\sum (x-\overline{x})(y-\overline{y})}{\sum {(x-\overline{x})}^2}$

And x, y are the sample means AVERAGE(known_x's) and AVERAGE(known y's).

Example:

Find the predicted value of y given x values of 25 and 52:

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

- Press Enter -> The predicted value of y is:

- Similarly, paste for the case where the value of x is 52 yields the result:

- In the case where the number of data points in the two arrays x and y known are different -> the function returns the error value #N/A

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

Above is the guide and some specific examples of using the FORECAST function in Excel.

Best wishes for your success!