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.

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

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