Tushar,

Thanks for that.

I took another look at the code I submitted.
The data type of the array was wrong ...

Dim arrNumbers() As Long SHOULD BE Dim arrNumbers() As Double

Regards,
Jim Cone
San Francisco, USA


"Tushar Mehta" wrote in
message ...
What you are doing is using the standard method taught in introductory
classes for calculating the S.D. by hand.
The variance is (Sum(X^2f)-Sum(Xf)^2)/Sum(f))/(Sum(f)-1) and the S.D.
is sqrt(Var)
While it is excellent for use 'by hand,' working from first principles
is superior when used with a computer. Doing so also removes the need
for the Xf and X^2f columns.
Given the first 2 columns, the average, mu, is Sum(Xf)/Sum(f). You can
calculate this with with just the X and f columns.
To get the variance, use Sum((X-mu)^2*f)/(Sum(f)-1). As for the mean,
you can calculate this with just the X and f columns.
Suppose the X data are in B3:B10 and the f data in C3:C10.
Then the average, mu, in, say cell D16 is
=SUMPRODUCT(B3:B10,C3:C10)/SUM(C3:C10)
Of course, if you have the Xf column, it is also equal to Sum(Xf)/Sum
(f)
Now, the Variance, in, say, cell H16, is
=SUMPRODUCT((B3:B10-$D$16)^2,C3:C10)/(SUM(C3:C10)-1)
And, of course, the S.D. is =SQRT(H16)
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


In article ,
!o!m! N!OT! says...
From a histogram I collected the following data, I'm trying to figure out
how to calculate the standard deviation of hours driving. I can do it
outside of using the stdev formula, but if I try to use the formula I mess
up my results.
Mean hrs= number of subjects/total hours driving (200/1580) = 7.9
Standard Deviation for hours driving: Needs to equal 1.05 I can get that by
taking 12702(power1580,2)/200 which = 220
taking that sum (220) and making another calculation. sqrt(220/200-1)
Those combined net the standard deviation, but there has to be an easier
way.... Any help would be appreciated.
x f xf x2f
Hours of driving Number of Subjects Total Hours driving
3.5 2 7 24.5
4.5 2 9 40.5
5.5 4 22 121
6.5 22 143 929.5
7.5 64 480 3600
8.5 90 765 6502.5
9.5 14 133 1263.5
10.5 2 21 220.5

56 199 1580 12702