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

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

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

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

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

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!

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.

Conrad Carlberg wrote...
....
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) . . .

Wrong in my case, so your rules of conduct don't apply to me (THANK
GOD!).

. . . to adopt an encouraging tone.

You see someone trying to light a barbecue in a healthy wind. Their
matches keep blowing out. You consider using your body as a shield
against the wind. Then you notice the the other person poored gasoline
on the charcoals.

Which of the following is the best course of action?
1. Pray quickly & loudly.
2. Suggest using foot long matches.
3. Walk quickly away.
4. Suggest that there may be a better way to cook on a barbecue?

Certainly the last could be seen as dismissive by anyone not aware that
the charcoals are soaked in gasoline, or anyone who's more concerned
with hurt feelings and bodily injury, but those other persons'
perspective is flawed.

There are times when VBA is necessary. Calculating simple stats isn't
one of those times. There are better ways to do it without VBA when
Excel is handy.

Maybe this doesn't apply to you, but when I respond to OPs I give them
the FULLNESS of my experience, which includes my opinion when their
methodology isn't ideal. If you don't like that, tough.

. . . 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.

Be however you want to be in your own responses. And I'll be how I want
in mine, which isn't always going to be to your liking.

Clearly if all I really wanted to do was calculate for myself the RSD of a
few numbers, I wouldn't be going to such great lengths to write a program for
it. I work with some people that aren't quite as adept with Excel or with
statistics or with algebraic manipulation, so to tell them to make a
spreadsheet and calculate the RSD of some numbers typically causes more work
for me than simply doing it myself.

I was hoping to be able to write a "quick" program that I could install as
an add-in on their machines so they could just type =RSD(cells) instead of

=stdev(cells)/average(cells)*100

which leaves lots of room for errors in remembering the function for
standard deviation and for mean. One function, easily titled RSD as it is
generally referred to, is much simpler to remember and enter than a 3-part
equation with two functions, each with names different than what they are
typically referred to.

I realize it didn't turn out to be as quick as I'd hoped once I ran into the
problem of not knowing how to define the arguments.

"Harlan Grove" wrote:

Conrad Carlberg wrote...
....
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) . . .

Wrong in my case, so your rules of conduct don't apply to me (THANK
GOD!).

. . . to adopt an encouraging tone.

You see someone trying to light a barbecue in a healthy wind. Their
matches keep blowing out. You consider using your body as a shield
against the wind. Then you notice the the other person poored gasoline
on the charcoals.

Which of the following is the best course of action?
1. Pray quickly & loudly.
2. Suggest using foot long matches.
3. Walk quickly away.
4. Suggest that there may be a better way to cook on a barbecue?

Certainly the last could be seen as dismissive by anyone not aware that
the charcoals are soaked in gasoline, or anyone who's more concerned
with hurt feelings and bodily injury, but those other persons'
perspective is flawed.

There are times when VBA is necessary. Calculating simple stats isn't
one of those times. There are better ways to do it without VBA when
Excel is handy.

Maybe this doesn't apply to you, but when I respond to OPs I give them
the FULLNESS of my experience, which includes my opinion when their
methodology isn't ideal. If you don't like that, tough.

. . . 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.

Be however you want to be in your own responses. And I'll be how I want
in mine, which isn't always going to be to your liking.

ASokolik wrote...
Clearly if all I really wanted to do was calculate for myself the RSD of a
few numbers, I wouldn't be going to such great lengths to write a program for
it. I work with some people that aren't quite as adept with Excel or with
statistics or with algebraic manipulation, so to tell them to make a
spreadsheet and calculate the RSD of some numbers typically causes more work
for me than simply doing it myself.

I was hoping to be able to write a "quick" program that I could install as
an add-in on their machines so they could just type =RSD(cells) instead of

=stdev(cells)/average(cells)*100
....

Give these other people a template file. Put a clear entry range in it.
Best to use a different background color, and borders don't hurt
either. Then put the formula

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

in a prominent place near the entry range. All they'd need to do is
enter the numbers, and your template would give them the descriptive
statistics.

While giving them a udf to use rather than two built-in functions in a
3-term formula may reduce the support headache somewhat, you'll have
the pleasure of different support headaches if any of these other users
have macro security set to High.

Also, as I already mentioned, Excel can evaluate

=100*STDEV(Sheet1:Sheet100!A1:Z2000)/AVERAGE(Sheet1:Sheet100!A1:Z2000)

There's no clean way to use 3D references with your udf that wouldn't
introduce greater complications than telling them to use built-in
functions.

Yes, VBA can play a significant role in developing cleaner workbooks.
Whether it helps in a case of replacing 2 simple functions with one UDF
is debatable. Nonetheless, if you do want to do down this road, you
can still leverage the XL object model. Untested code:

Public Function RSD(ParamArray X() As Variant)
With Application.WorksheetFunction
RSD = .StDev(X) / .Average(X) * 100
End With
End Function

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Clearly if all I really wanted to do was calculate for myself the RSD of a
few numbers, I wouldn't be going to such great lengths to write a program for
it. I work with some people that aren't quite as adept with Excel or with
statistics or with algebraic manipulation, so to tell them to make a
spreadsheet and calculate the RSD of some numbers typically causes more work
for me than simply doing it myself.

I was hoping to be able to write a "quick" program that I could install as
an add-in on their machines so they could just type =RSD(cells) instead of

=stdev(cells)/average(cells)*100

which leaves lots of room for errors in remembering the function for
standard deviation and for mean. One function, easily titled RSD as it is
generally referred to, is much simpler to remember and enter than a 3-part
equation with two functions, each with names different than what they are
typically referred to.

I realize it didn't turn out to be as quick as I'd hoped once I ran into the
problem of not knowing how to define the arguments.

"Harlan Grove" wrote:

Conrad Carlberg wrote...
....
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) . . .

Wrong in my case, so your rules of conduct don't apply to me (THANK
GOD!).

. . . to adopt an encouraging tone.

You see someone trying to light a barbecue in a healthy wind. Their
matches keep blowing out. You consider using your body as a shield
against the wind. Then you notice the the other person poored gasoline
on the charcoals.

Which of the following is the best course of action?
1. Pray quickly & loudly.
2. Suggest using foot long matches.
3. Walk quickly away.
4. Suggest that there may be a better way to cook on a barbecue?

Certainly the last could be seen as dismissive by anyone not aware that
the charcoals are soaked in gasoline, or anyone who's more concerned
with hurt feelings and bodily injury, but those other persons'
perspective is flawed.

There are times when VBA is necessary. Calculating simple stats isn't
one of those times. There are better ways to do it without VBA when
Excel is handy.

Maybe this doesn't apply to you, but when I respond to OPs I give them
the FULLNESS of my experience, which includes my opinion when their
methodology isn't ideal. If you don't like that, tough.

. . . 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.

Be however you want to be in your own responses. And I'll be how I want
in mine, which isn't always going to be to your liking.

That was a dumb post to make most any time and especially in the midst
of a heated argument. ParamArray X() cannot simply be passed on to
StDev. :( It results in a Type mismatch. Sorry. Nonetheless, that
error doesn't negate the concept I wanted to focus on, i.e., leveraging
the XL object model and letting it do as much "heavy lifting" as
possible.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
hEm says...
Yes, VBA can play a significant role in developing cleaner workbooks.
Whether it helps in a case of replacing 2 simple functions with one UDF
is debatable. Nonetheless, if you do want to do down this road, you
can still leverage the XL object model. Untested code:

Public Function RSD(ParamArray X() As Variant)
With Application.WorksheetFunction
RSD = .StDev(X) / .Average(X) * 100
End With
End Function

Tushar Mehta wrote...
....
. . . Nonetheless, if you do want to do down this road, you
can still leverage the XL object model. Untested code:

Public Function RSD(ParamArray X() As Variant)
With Application.WorksheetFunction
RSD = .StDev(X) / .Average(X) * 100
End With
End Function

You didn't tortute test this. With A1:B2 containing {1,2;3,4},

=RSD(A1:B2) returns #VALUE!

=RSD({1,2},{3;4}) returns #VALUE!

=RSD({1;2}) returns #VALUE!

The problem is with the functions called via
Application.WorksheetFunctions. Those functions can handle ParamArray
arrays only in very limited situations. Either all arguments must be
simple numbers (scalars) or all simple horizontal arrays of the same
size. These functions can't even handle a single range argument if it's
wrapped as a ParamArray.

Given that, it would be much more efficient to write something like:

Public Function RSD(rng As Excel.Range) As Variant
Dim dAvg As Double
Dim dStdDev As Double
On Error GoTo ErrHandler
With Application
dAvg = .Average(rng)
If dAvg = 0 Then
RSD = CVErr(xlErrDiv0)
Else
RSD = 100 * .StDev(rng) / dAvg
End If
End With
Resume_He
Exit Function
ErrHandler:
RSD = CVErr(xlErrValue) 'can get more sophisticated here
Resume Resume_Here
End Function




In article ,
"ASokolik" wrote:

I was hoping to be able to write a "quick" program that I could install as
an add-in on their machines so they could just type =RSD(cells) instead of

=stdev(cells)/average(cells)*100

which leaves lots of room for errors in remembering the function for
standard deviation and for mean. One function, easily titled RSD as it is
generally referred to, is much simpler to remember and enter than a 3-part
equation with two functions, each with names different than what they are
typically referred to.

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