CODEWITHSUNDEEP
excelvbaworksheet-function
WATERflowTech
WATERflowTech

Reputation: 119

Root Mean Square (rms) function in VBA?

So I'm calculating basic statistics in my worksheet and it includes code such as:

xxx = Application.worksheetfunction.average(etc etc etc

yyy = Application.worksheetfunction.min(etc etc etc

zzz = Application.worksheetfunction.max(etc etc etc

My question: Is there an RMS equivalent function where I can simply plug it in place of where I have 'average, min, max' functions in that code? And if there isn't then what would be the most efficient means to code in to find RMS solutions?

I hope I've stated the goal clearly enough. I'm curious as to whether or not there is a predefined RMS function for VBA or whether or not I've got to create some sort of user defined function? ~ That of which I'm fairly new to as well so if there isn't a simple line of code to write for this, I'll have to do more reading on UDF's.

EDIT:

I've got around 30,000 rows, and for simplicity's sake: imagine two columns. Column A has the year i.e. 1941 or anything else through 2008. Column B is a numeric value. I'm just trying to put code together that gives decade summaries of Average, Min, Max, and the RMS values.

Upvotes: 5

Views: 8588

Answers (3)

Anderson Wanselowski
Anderson Wanselowski

Reputation: 1

This one worked for me:

Function RMS(Intervalo As Range)

Dim SomaQ As Double
Dim Tamanho As Integer

SomaQ = 0
Tamanho = Intervalo.Count
SomaQ = Application.WorksheetFunction.SumSq(Intervalo)
RMS = Sqr(SomaQ / Tamanho)

End Function

Upvotes: 0

Cor_Blimey
Cor_Blimey

Reputation: 3310

A VBA function that accepts arrays (any rank) and ranges with multiple areas (a discontinuous range like A4:B6,C11:D15), or even a union of ranges in a formula. It skips non number datatypes (including dates, boolean, blanks etc).

You can use it in VBA code, or as a UDF in a worksheet formula such as:

"=RMS(A1:A10)" (basic usage)

"=RMS(A1:A10,C1:C10)" (multiple ranges (or arrays for that matter))

"{=RMS({1,2,3,4})}" (array formula entered with Ctrl+shift+enter)

Function RMS(ParamArray args()) As Double

    Dim arg, arr, area As Range, ss As Double, n As Long

    For Each arg In args
        If TypeOf arg Is Range Then
            For Each area In arg.Areas
                arr = area.value
                If VarType(arr) < vbArray Then
                    queryRmsElements Array(arr), ss, n
                Else
                    queryRmsElements arr, ss, n
                End If
            Next area
        ElseIf VarType(arg) > vbArray Then
            queryRmsElements arg, ss, n
        Else
            Err.Raise 1, "RMS", "Invalid Argument"
        End If
    Next arg

    RMS = (ss / n) ^ 0.5

End Function
Private Sub queryRmsElements(ByRef elements, ByRef ss As Double, ByRef n As Long)
    Static element As Variant
    'Enumerate to cover rank > 1 (vs. Iterate)
    For Each element In elements
        Select Case VarType(element)
            Case VbVarType.vbByte, _
                 VbVarType.vbCurrency, _
                 VbVarType.vbDecimal, _
                 VbVarType.vbDouble, _
                 VbVarType.vbInteger, _
                 VbVarType.vbLong, _
                 VbVarType.vbSingle
                 ss = element ^ 2 + ss
                 n = n + 1
            Case Else
        End Select
    Next element
End Sub

Upvotes: 0

Lance Roberts
Lance Roberts

Reputation: 22842

You can do the average with

=SQRT(SUMSQ(A:A)/COUNTA(range))

or in VBA:

r = (Application.WorksheetFunction.SumSq(Range("A:A")) / Range("A:A").Count) ^ (1 / 2)

Upvotes: 1

Related Questions