+ Reply to Thread
Results 1 to 8 of 8

Calculate the Mean Absolute Error Using VBA

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    42

    Calculate the Mean Absolute Error Using VBA

    Hi Everyone,

    I'm trying to develop a VBA Function that can calculate the mean absolute error of a range of values. the way the funtion would need to do is the following:

    1. There are two columns of data; Column A and Column B. Both Columns have the same number of rows of data.

    2. For every row of data the function would need to do Column A - Column B, take the absolute value of the result, and store it in an Array

    3. The function would then calculate the average of all the values in the Array, and return a result.

    Would anyone know how to do this?

    Thanks.

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,294

    Re: Calculate the Mean Absolute Error Using VBA

    will this suit you?

    Please Login or Register  to view this content.
    this makes a few assuming that can be cleaned up....like LastRow is the number of items. It may not be but that can be fixed....
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm alway close enough to walk....

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,747

    Re: Calculate the Mean Absolute Error Using VBA

    How about just a formula?

    =average(abs(a1:a10-b1:b10))

    It MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    03-13-2014
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Calculate the Mean Absolute Error Using VBA

    Thank you. that works. I didnt know about the Ctrl+Shift+Enter... that's a nice trick

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,747

    Re: Calculate the Mean Absolute Error Using VBA

    You're welcome.

  6. #6
    Registered User
    Join Date
    03-13-2014
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Calculate the Mean Absolute Error Using VBA

    Thank you! Your Code was a good start. I made a few changes to suit my spreadsheet but the consept is the same. thank you!

    My version of the code is below for anyone who wants to look:

    Function MAE(FirstRow, LastRow, a As Long) ' MAE stands for Mean Absolute Error
    'a is the first column of data
    Dim NumberOfRows As Long
    Dim TempNumber As Single
    Dim i As Long ' Row Counter

    NumberOfRows = LastRow - FirstRow + 1 ' You have to add 1 or you will be one row short
    TempNumber = 0
    i = FirstRow

    Do While i <= LastRow
    TempNumber = TempNumber + Abs(Cells(i, a) - Cells(i, a + 1))
    i = i + 1
    Loop

    MAE = TempNumber / NumberOfRows

    End Function

  7. #7
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,294

    Re: Calculate the Mean Absolute Error Using VBA

    no problem....glad we could help....please remember to set this to SOLVED....If you feel we deserve an increase in my reputations feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be one of us...just a thought...
    Last edited by judgeh59; 03-14-2014 at 04:48 PM.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,747

    Re: Calculate the Mean Absolute Error Using VBA

    The problem with your code, aside from that it has no code tags, is that Excel sees no dependency on the range from which the result is computed, so changing the range will not cause the function to recalculate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1