Help calculating Root Mean Square Error

[aborania]

Hello,

I am trying to create a VBA function that will read in a 1-D array from Excel in order to calculate RMSE. The values in the array I am trying to read is dCOP, the difference between how an A/C unit actually performs and how it performs in a computer model. My code is as follows:

Function RMSE(ByRef dCOP() As Long) As Long
Dim n As Long
Dim Sum As Long
For n = LBound(dCOP) To UBound(dCOP)
Sum = Sum + (dCOP ^ 2)
Next n
RMSE = Application.WorksheetFunction.Sqrt(Sum)
End Function

I keep getting a #NAME? error. Any ideas on what to change?

Thanks in advance.

 

[shg]

If all values in the range will be numbers, you could use this:

Function RMS1(r As Range) As Variant
    ' Returns the RMS (the square root of the
    ' average of the squared values) in r
 
    ' All values in r must be numbers
 
    With WorksheetFunction
        If .Count(r) <> r.Cells.Count Then
            RMS1 = CVErr(xlErrValue)
        Else
            RMS1 = Evaluate("sqrt(average(" & r.Address & "^2))")
        End If
    End With
End Function

If not,

Function RMS2(r As Range) As Variant
    ' Returns the RMS (the square root of the
    ' average of the squared numbers) of r
    Dim n           As Long
    Dim ad()        As Double
    Dim i           As Long
    Dim cell        As Range
 
    With WorksheetFunction
        n = .Count(r)
        If n = 0 Then
            RMS2 = CVErr(xlErrDiv0)
 
        Else
            ReDim ad(1 To n)
            For Each cell In r
                If VarType(cell.Value2) = vbDouble Then
                    i = i + 1
                    ad(i) = cell.Value2 ^ 2
                End If
            Next cell
 
            RMS2 = Sqr(.Average(ad))
        End If
    End With
End Function

 

[aborania]

Thanks for the reply.

If I use your code and modify it slightly to fit what I need, this is what I have:

Function RMS1(r As Range) As Variant
    ' Returns the RMSE (the square root of the
    ' sum of the squared values over the number of instances) in r
 
    ' All values in r must be numbers
 
    With WorksheetFunction
        If .Count(r) <> r.Cells.Count Then
            RMS1 = CVErr(xlErrValue)
        Else
            RMS1 = Evaluate("sqrt(sum(" & r.Address & "^2))/.Count(r)")
        End If
    End With
End Function

Now I am getting a #REF! error, and I do with the original code as well. Do I need to select a specific VBA Reference under the Tools menu?

 

[shg]

Sorry, I tested in Excel 2003; in 2007+, RMS1 is a cell address. Change the name to something else.

Also, your Evaluate string won't evaluate; I don't see what you're trying to do.

 

[aborania]

Thanks a ton, works like a charm. You have just made my thesis research MUCH easier and you are the smartest man alive.

 

[shg]

You're welcome, glad it worked for you.

Actually, this is bettter and will work if there are blanks or text.

 Function RMS(r As Range) As Double
    ' Returns the RMS (the square root of the
    ' average of the squared values) in r
    With WorksheetFunction
        RMS = Sqr(.SumSq(r) / .Count(r))
    End With
End Function