Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Function to calculate MAPE

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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





  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    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)

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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