In this tutorial, we will look at three related functions that can be used to predict future unknown values – based on existing data.
The FORECAST function takes a set of X-Y data pairs and returns a single Y value – given it’s corresponding X-value. The assumption is that the data is linear in behaviour.
In the example below, we have sales data for the first eight months of 2004. We want to predict (forecast) the sales figures for July 2005.
The first argument is the X value for which we need a forecast. The second and third arguments are the known Y values and known X values respectively.
The TREND function issimilar to FORECAST. A linear relationship is assumed between the X and Y data sets.
Howver, TREND is an array function and returns an array of unknown Y values.
The frst two arguments to TREND are the know X and Y data sets. The third argument is the array of X values for which we wish to predict the corresponding Y data.
As you can see from the chart below, OOo Calc ignore anomalous data as best it can in predicting future values for Y.
Like TREND, the GROWTH function returns an array of predicted values. The difference being that exponential growth is assumed. Otherwise the format is the same.
Again, by charting the known and predicted data, you can decide if OOo Calc has made a reasonable prediction based on the data provided to it.