How to write STDev formula in a macro

[Chua]

I have a list of data over 600 points to calculate the STDev.

Eg. Range A1:IR252 and Range A9:IR252

Anyone has idea how to write a macro to detect the different rows of values
and calculate the STDev using VBA?

Thanks

 

[Bob Bridges]

Seems to me the very simplest way would be to use
Application.WorksheetFunctions.StDev. If you do that, you can supply a
range... Oh, I see; it accepts only 30 values. I guess you'll have to run
through a loop doing the standard-deviation calculation.

So is it a loop that you're not sure how to write, or is it the standard
deviation itself? It seems to me it must be something about VBA macros,
because anyone who wants to use a standard deviation probably already knows
how to obtain it, right?

If so, what part of VBA macros are you unsure of? Have you ever written a
program at all? Ever written anything at all in VBA? I mean, where are we
starting?

 

[Chua]

As you mentioned, it can only accept 30 values.......I have no problem
writing the loop....just want to check what is the formula for STDev.

Thanks

 

[Mike H]

Hi,

It doesn't accept 30 values, it accepts 30 arguments so

=stdev(a1:a1000) is 1 argument and not 1000

Mike

 

[Bob Bridges]

Now that's a distinction I wasn't aware of but that seems obvious now that
you point it out. Ok, so Chua, you can use the StDev worksheet function
after all, I guess, eh?

 

[Bob Bridges]

Oh, I'm surprised. I just figured anyone who wanted to use a standard
deviation must already know how to calculate it. But the formula is listed
already in the Excel documentation for that function. Just hit Help and ask
for STDEV, and you'll see it right there.

 

[Bob Bridges]

Wait, I just looked at the documentation again: It accepts 30 NUMBER
arguments -- to be precise, "1 to 30 number arguments corresponding to a
sample of a population. You can also use a single array or a reference to an
array instead of arguments separated by commas". So A1:A1000 wouldn't work,
probably.

 

[Chua]

I saw that....but not sure how the loop is been written...seems like
difficult if I have a collection of data > 600......it's a headache!!!!

I tried writing a loop to STDEV() range all the cells but found that the
answer is the same be it 30 values or 600 values......based on excel
explanation, could it be data > 30 will be insignifiant for the STDEV
calculation.

Anyone can help???

Thanks

 

[Bob Bridges]

I'm a little suspicious, Chua. When I supposed you knew how to do the
calculation and offered help with writing the loop, you said "I have no
problem writing the loop....just want to check what is the formula for
STDev". But when I pointed to the formula for StDev, you say "I saw
that....but not sure how the loop is been written".

Maybe we should look at what you've written so far. What does your loop
look like, and what happens when you try to run it?

 

[Ron Rosenfeld]

I have a list of data over 600 points to calculate the STDev.

Eg. Range A1:IR252 and Range A9:IR252

Anyone has idea how to write a macro to detect the different rows of values
and calculate the STDev using VBA?

Thanks

Your ranges overlap, but if you are doing both:

Debug.Print Application.WorksheetFunction.StDev( _
Range("A1:IR252"), Range("A9:IR252"))

--ron

 

[Chua]

Hi Mike,

You are right!!!

Thanks for solving my problem...now my program works perfectly.

Cheers
Chua