Function to calculate MAPE

[Lucas in London]

Hi there,

I'm trying to write a function in excel which will calculate the mean absolute percentage error (MAPE) for a variable. Basically this is a measure of forecast accuracy, which compares forecasts for a variable against actual values.

For those not familiar with this measure, basically I need a function that will calculate the absolute percentage difference between the values for two variables and return the average or sum of these:

For example say we have two variables (X and Y) with following values:

X Y The pct difference =
7 7 0
10 11 10
15 21 40
Average = 16.6
MAPE = 16.6

I know how to do these calculations within a spreadsheet but I want to do using a custom function. Also I know how to create inputboxes for the user to select the ranges for X and Y but I just haven't got a clue how to write the actual basic formula to calculate differences in values between two arrays and calculate statistics on these.

I hope someone out there can help me.

Many Thanks

Lucas in currently a sunny London

 

[Andrew Poulsom]

Insert a module in your workbook and paste the following code:

Function MAPE(Original As Range, Revised As Range)
    Dim Divisor As Long
    Dim x As Long
    Dim Change As Double
    Dim TotalChange As Double
    Divisor = Original.Rows.Count
    Change = 0
    TotalChange = 0
    For x = 1 To Divisor
        Change = Abs(((Revised.Cells(x, 1) / Original.Cells(x, 1)) - 1) * 100)
        TotalChange = TotalChange + Change
    Next x
    MAPE = TotalChange / Divisor
End Function

Assuming your X data is in cells A1:A3 and your Y data is in cells B1:B3, enter the following formula in any other cell:

=MAPE(A1:A3,B1:B3)

 

[Juan Pablo González]

Lucas, my first option would be to use an Array formula, like this

=AVERAGE((B1:B3-A1:A3)/A1:A3)

or, if you want the UDF, then, the same concept.

<pre>Function MAPE(X As Range, Y As Range) As Variant
MAPE = Evaluate("AVERAGE((" & Y.Address(0, 0) & "-" & X.Address(0, 0) & ")/" & X.Address(0, 0) & ")")
End Function</pre>