 Thanks:  0 Likes:  0

# Thread: Function to calculate MAPE

1. ## 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  Reply With Quote

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

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)  Reply With Quote

3. ## 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.

```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```  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•