Statistical Linear Trend Analysis of Data with Excel Forecast Function

The Excel Forecast is one of the Statistical Function that predicts a future point on a linear trend line fitted to a supplied set of x- and y- values.

**Syntax:**

=FORECAST( x, known_y's, known_x's )

Where the arguments are as follows :

**x**

**-**A numeric x-value for which you want to forecast a new y-value

**known_y's**

**-**An array of known y-values.

**known_x's**

**-**An array of known x-values.Note that the length of the known_x's array should be the same length as known_y's, and the variance of the known_x's must not be zero.

**Example:**

The spreadsheet below shows an example of the Forecast function used to predict an additional point(G8 for F8 Where F8=7 for which we find G8 as shown in the following image) along the straight line of best fit through the known_x's and known_y's in cells F2:F7 and G2:G7. These known points are shown in the graph on the same spreadsheet.

The above example gives the result 32.666667.

**Forecast Equations:**

The Excel Forecast Function calculates the new y-value from the simple equation for a straight line :

where,

and

and the values of x and y are the sample means (the averages) of the known_x's and the known_y's.

**Forecast Function Errors:**

If your Forecast function gives you an error message, use the table below to look up the likely cause:

**#VALUE!**- Produced if the supplied future x value is non-numeric

**#NA**- Occurs if there are problems with the supplied known_x's or known_y's arrays - i.e. if either of these arrays is empty, or if the arrays are of different lengths.

**#DIV/0!**- Produced if the variance of the supplied known_x's evaluates to zero.

Source:

excelfunctions.net

## No comments:

## Post a Comment