Saturday, 16 March 2013

Excel Forecast Function for Linear Trend Analysis of Statistical Data

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.

Forecast Equations:
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.


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts