# Calculate the Mean Absolute Error Using VBA

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

3. ## Re: Calculate the Mean Absolute Error Using VBA

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

It MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

4. ## 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. ## Re: Calculate the Mean Absolute Error Using VBA

You're welcome.

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

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

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