Hello:

Don't know the direct answer to your question, but here is a routine that
calculates the mean for any range passed to it:

(Note it does not do any error checking, which you might want to add. As
an example it will not ignore blank values as the built in average()
function does.)


Function Mean(apple As Range) As Double
Dim sum As Double
Dim n As Integer
n = Application.Count(apple)
sum = 0
For i = 1 To n
sum = sum + apple(i)
Next i
Mean = sum / n
End Function

Example

a8: 6
a9: 17
a10: 19
a11: 21
a12: 15.75 =mean(A8:A11)

Pieter Vandenberg

lantern20 wrote:
: ASokolik
: Did you ever get this function to work? If you did, would you be able to
: provide it? I would find it very useful. I am not experienced in
: programming so I don't know how to do it myself.

: Thanks very much
: Regards

: lantern20

: "ASokolik" wrote:

: I am trying to create a function in VBA (possibly an add-in in the future) to
: calculate Relative Standard Deviation (RSD). I would like to be able to use
: an equation such as: =RSD(values), where the values are cell references (a
: variable quantity) chosen by the user.
:
: To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
: function for RSD that calls those two. When I try to implement RSD in the
: worksheet (or even just Mean), the cell returns an error: #VALUE! I think
: the problem has something to do with the Arr() variable used as the argument,
: as I've tried some simpler functions with array arguments and can't get them
: to work, but using standard non-array arguments works fine. I can write a
: subroutine that calls the functions and displays the correct result, but I
: would like to be able to use RSD as a typical function in my worksheet.
:
: If I could get Mean to work properly, I should be able to apply that
: knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
: quite a few modifications to try to make it work, so if someone could get it
: to work and reply with the correct function text (or with another function
: that performs similarly), I'd appreciate it. Thanks.
:
: Function Mean(Arr() As Single)
: Dim Sum As Single
: Dim i As Integer
:
: Sum = 0
: For i = 1 To UBound(Arr)
: Sum = Sum + Arr(i)
: Next i
:
: Mean = Sum / UBound(Arr)
: End Function