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.
=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.
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.
The Excel Forecast Function calculates the new y-value from the simple equation for a straight line :
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.