Search code examples
excelvbaworksheet-function

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.

Solution

  • 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)