Custom Function for % Relative Standard Deviation

[smpatty08]

I am trying to create a custom function for %RSD. The formula for %RSD is the (Standard Deviation of a range/Average of the same range)*100. this is normally done in excel by using the STDEV and AVERAGE functions, but I would like a function the will do it by just selecting the range.

=RSD(A1:A6)

Here is the code I have so far, but I keep getting errors.

Function RSD(rng As Range)
Dim cell As Integer
For Each cell In rng
    If cell <> 0 And IsNumeric Then
        RSD = ((stdev(rng) / Average(rng)) * 100)
    Else
        MsgBox ("One or more cells in selected range is blank or not numeric.")
    End If
Next cell
End Function

I am familiar with VBA, but I have never made a custom function. Any help would be appreciated. THANKS!

 

[jmacleary]

Hi there

Ihave changed your code as follows - I think it works!:

Function RSD(rng As Range)
Dim cell As Range

For Each cell In rng
    If cell.Value <> 0 And IsNumeric(cell.Value) Then
        RSD = ((Application.WorksheetFunction.StDev(rng) / Application.WorksheetFunction.Average(rng)) * 100)
    Else
        MsgBox ("One or more cells in selected range is blank or not numeric.")
    End If
Next cell
End Function

 

[louisH]

Hi.
I see some mistakes here :

Dim cell As Integer
For Each cell In rng

cell is defined as an integer but used as a Range in the For Each loop.
Try defining cell as a Range.

Dim cell As Range

The IsNumeric function is waiting a parameter.
stdev and average are worksheet functions, so you need to do WorksheetFunction.Function
You need to define the output of your function (integer, double, ...) like this :

Function functionname() As Double

These will normally remove the errors.

Also if I may, your function is doing too much calculation. You re-calculate your output for each cell.

Try this :

Function RSD(rng As Range) As Double
Dim cell As Range
For Each cell In rng
    If cell.Value = "" Or IsNumeric(cell) = False Then
MsgBox "One or more cells in selected range is blank or not numeric."
Exit Function
End If
        

Next cell
RSD = ((WorksheetFunction.stdev(rng) / WorksheetFunction.Average(rng)) * 100)
End Function

 

[smpatty08]

Thank you both! Both versions work as expected

 

[jmacleary]

You're welcome, although I would use LouisH's version because, as stated, my solution would unnecessarily process the entire range.