FORECAST(x, array_y, array_x)

The FORECAST function returns a prediction of y for a given x after first performing a linear regression on the data points described by array_y and array_x.

A linear regression produces a "best fit" line through the data points by minimizing the sum of the (squared horizontal) distances from each data point to the line. FORECAST then uses this line to predict a y value for the supplied x as follows:

image\ebx_1438168149.gif

which is the same as:

y = INTERCEPT(array_y, array_x) + SLOPE(array_y, array_x)*x

For example, suppose the following y-values were observed for the supplied x-values:

Y

4.7

6.0

11.2

10.6

8.2

7.3

15.8

11.7

X

1

2

3

4

5

6

7

8

A linear regression through these points would look like this:

image\ebx_345652550.gif

You could use FORECAST to predict the y value at x = 10 using the following formula:

FORECAST(10, {4.7, 6, 11.2, 10.6, 8.2, 7.3, 15.8, 11.7}, {1, 2, 3, 4, 5, 6, 7, 8})

which returns the value 14.9.

See also:

INTERCEPT(array_y, array_x)

SLOPE(array_x, array_y)

STEYX(array_y, array_x)

Other statistical functions