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

Hi ASokolik --

You're right, it has to do with what you're passing as an argument -- at
least, I assume that when you call the Mean function that you're using
something such as this:

=Mean(A1:A10)

But A1:A10 is a range, not a memory array. Try something like:

Option Base 1
Option Explicit

Function Mean(rng As Range) As Single

Dim Sum As Single
Dim i As Integer
Dim arr() As Single

Sum = 0
ReDim arr(rng.Cells.Count)
For i = 1 To UBound(arr)
arr(i) = ActiveSheet.Cells(i, 1)
Next i
For i = 1 To UBound(arr)
Sum = Sum + arr(i)
Next i

Mean = Sum / UBound(arr)
End Function

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"ASokolik" wrote in message
...
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

That did it! Thanks, Conrad!

"Conrad Carlberg" wrote:

Hi ASokolik --

You're right, it has to do with what you're passing as an argument -- at
least, I assume that when you call the Mean function that you're using
something such as this:

=Mean(A1:A10)

But A1:A10 is a range, not a memory array. Try something like:

Option Base 1
Option Explicit

Function Mean(rng As Range) As Single

Dim Sum As Single
Dim i As Integer
Dim arr() As Single

Sum = 0
ReDim arr(rng.Cells.Count)
For i = 1 To UBound(arr)
arr(i) = ActiveSheet.Cells(i, 1)
Next i
For i = 1 To UBound(arr)
Sum = Sum + arr(i)
Next i

Mean = Sum / UBound(arr)
End Function

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"ASokolik" wrote in message
...
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

Oops. Bonehead. Instead of:

arr(i) = ActiveSheet.Cells(i, 1)

you'd want something like:

arr(i) = rng.Cells(i)
--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005

I'm not sure why you're not using XL's built in AVERAGE() formula. I
suppose you could use a wrapper function:

Public Function Mean(vArr As Variant)
Mean = Application.Average(vArr)
End Function

but I don't see the point.


In article ,
"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

JE McGimpsey wrote...
I'm not sure why you're not using XL's built in AVERAGE() formula. I
suppose you could use a wrapper function:

Public Function Mean(vArr As Variant)
Mean = Application.Average(vArr)
End Function

but I don't see the point.
....

And all to calculate relative standard deviation, which is just

=100*STDEV(rng)/AVERAGE(rng)

or, for older pathological versions of Excel,

=100*SQRT(DEVSQ(rng)/(COUNT(rng)-1))/AVERAGE(rng)

Note that it's a worthless statistic when rng could have a nonpositive
mean.

The point that *I* don't see is why you're all beating up on ASokolik. Has
it not occurred to you that he or she might be trying to develop some
further expertise in VBA? Or are you the only people allowed to know what
you're doing? This sort of thing is why public newsgroups are so damned
ugly.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"Harlan Grove" wrote in message
oups.com...
JE McGimpsey wrote...
I'm not sure why you're not using XL's built in AVERAGE() formula. I
suppose you could use a wrapper function:

Public Function Mean(vArr As Variant)
Mean = Application.Average(vArr)
End Function

but I don't see the point.
...

And all to calculate relative standard deviation, which is just

=100*STDEV(rng)/AVERAGE(rng)

or, for older pathological versions of Excel,

=100*SQRT(DEVSQ(rng)/(COUNT(rng)-1))/AVERAGE(rng)

Note that it's a worthless statistic when rng could have a nonpositive
mean.

Ouch.
Conrad,
I think it is a shame you read the responses in the way you did.
They didn't come across as having any negative sentment to me.

I think it is a well recognised shortcomming, of the typed word that
sentements are often missunderstood.
Short of us all typing <g, <vbg and others through our text, this is
unlikely to change

My experience as a 'dabbler' in these excel groups is that, with the
exception of an odd Access Fixated Troll, regular contributers wants to
help and share knowledge in a positive way.

If I am wrong, and they are here to show off and brag, that would be
pitiful. Somehow I don't think that is the case.

be happy
RES

Hi Robert,

Ouch.

I hope so. I appreciate your reply, although we disagree as to the nature of
others' contributions to this thread (FWIW, I thought that Alan Beban's
reply was right on the button). I hope we can disagree without being
disagreeable.

Of course you're right, it can be difficult to discern the sense, or
sentiments, of a typed reply. That's why it's incumbent on someone as
generally well regarded as Messrs. McGimpsey and Grove to take care with the
tenor of their responses. I regard them (their responses in this thread,
that is) as dismissive, and I think it's particularly important for an Excel
MVP (I think that they both have and deserve that designation) to adopt an
encouraging tone. Before I resigned from the MVP program, I tried hard to do
so. Particularly in an unmoderated newsgroup, I think it's important to take
care with our responses. Perhaps I failed to do so myself.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


wrote in message
...
Ouch.
Conrad,
I think it is a shame you read the responses in the way you did.
They didn't come across as having any negative sentment to me.

I think it is a well recognised shortcomming, of the typed word that
sentements are often missunderstood.
Short of us all typing <g, <vbg and others through our text, this is
unlikely to change

My experience as a 'dabbler' in these excel groups is that, with the
exception of an odd Access Fixated Troll, regular contributers wants to
help and share knowledge in a positive way.

If I am wrong, and they are here to show off and brag, that would be
pitiful. Somehow I don't think that is the case.

be happy
RES

Conrad Carlberg wrote...
The point that *I* don't see is why you're all beating up on ASokolik. Has
it not occurred to you that he or she might be trying to develop some
further expertise in VBA? Or are you the only people allowed to know what
you're doing? This sort of thing is why public newsgroups are so damned
ugly.

Fine.

Reinventing the wheel is wonderful!

Slow, error-prone VBA is much better than relatively proven built-in
functions because there'd be so many mistakes from which to learn!

Learning how to do X is far more important than learning which X to do
or not!

How are you calling the function?

Alan Beban

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

I'm looking for maximum flexibility as I call it. What that means is that
I'd like it to respond in basically the same way as the Average function,
where I can type in =average(a1:a10) or =average(a1,a5,b4,g32) and it will
average all of the cells I select. The range feature works very well for the
first, but if instead I want to select a "list" of cells (not sure if it
would technically be referred to as a list), it doesn't work.

This applies to the codes that both Conrad and JE McGimpsey left. Is there
another form of argument that would allow me to enter either a range or a
smattering of random cells as inputs? I tried "As List", but that didn't do
it.

By the way, all of the hints have been helpful, as Conrad's code gave me
extra insight into calling ranges and setting up arrays (and even before your
second reply I was making my own changes to fix the array references), while
JE's code showed me how to use XL's built-in functions within my own VBA
scripts (I was unaware that they were nested under Application).

Thanks again.

"Alan Beban" wrote:

How are you calling the function?

Alan Beban

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

ASokolik wrote...
....
This applies to the codes that both Conrad and JE McGimpsey left. Is there
another form of argument that would allow me to enter either a range or a
smattering of random cells as inputs? I tried "As List", but that didn't do
it.

ParamArray. Read about its requirements and limitations in online help.

That said, the built-in AVERAGE worksheet function can accomodate 3D
references as well, e.g., Sheet1:Sheet5!X99:Z100. There's NO WAY to
pass 3D references to VBA functions except as text, and no way to use
them except in Evaluate calls with formulas constructed as strings.
This is MUCH SLOWER and MORE ERROR PRONE than performing the same
calculations in worksheet cell formulas.

In other words, what you've described you want to do is something for
which VBA is CLEARLY INFERIOR to worksheet cell formulas.

But the choice is yours.

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

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