Calculates the mean absolute percentage error (Deviation) function for the forecast and the eventual outcomes.
Syntax
MAPE(X, Y, Ret_type)
X is the original (eventual outcomes) time series sample data (a one dimensional array of cells (e.g. rows or columns)).
Y is the forecast time series data (a one dimensional array of cells (e.g. rows or columns)).
Ret_type is a switch to select the return output (1=MAPE (default), 2=Symmetric MAPE (SMAPI)).
Order  Description 

1  MAPE (default) 
2  SMAPE 
Remarks
 MAPE is also referred to as MAPD.
 The time series is homogeneous or equally spaced.
 For a plain MAPE calculation, in the event that an observation value (i.e. $x_k$) is equal to zero, the MAPE function skips that data point.
 The mean absolute percentage error (MAPE), also known as mean absolute percentage deviation (MAPD), measures the accuracy of a method for constructing fitted time series values in statistics.
 The two time series must be identical in size.
 The mean absolute percentage error (MAPE) is defined as follows:
$$\mathrm{MAPE}=\frac{100}{N}\times \sum_{i=1}^N \left  \frac{x_i  \hat x_i}{x_i} \right $$
Where:
 $\{x_i\}$ is the actual observations time series
 $\{\hat x_i\}$ is the estimated or forecasted time series
 $N$ is the number of nonmissing data points
 When calculating the average MAPE for a number of time series, you may encounter a problem: a few of the series that have a very high MAPE might distort a comparison between the average MAPE of a time series fitted with one method compared to the average MAPE when using another method.
 In order to avoid this problem, other measures have been defined, for example the SMAPE (symmetrical MAPE), weighted absolute percentage error (WAPE), real aggregated percentage error, and relative measure of accuracy (ROMA).
 The symmetrical mean absolute percentage error (SMAPE) is defined as follows:
$$\mathrm{SMAPE}=\frac{200}{N}\times \sum_{i=1}^N \frac{\left  x_i  \hat x_i \right }{\left  x_i\right +\left  \hat x_i\right } $$  The SMAPE is easier to work with than MAPE, as it has a lower bound of 0% and an upper bound of 200%.
 The SMAPE does not treat overforecast and underforecast equally.
 For a SMAPE calculation, in the event the sum of the observation and forecast values (i.e. $x_k + \hat x_k$) equals zero, the MAPE function skips that data point.
Examples
Example 1:


Formula  Description (Result)  

=MAPE($B$1:$B$19,$C$1:$C$19,1)  MAPE (151.503)  
=SMAPE($B$1:$B$19,$C$1:$C$19,2)  SMAPE (500) 
Files Examples
References
 Hamilton, J .D.; Time Series Analysis , Princeton University Press (1994), ISBN 0691042896
 Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0471690740
0 Comments