STDEV...HELP

[JRH]

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

 

[JRH]

Okay, let try this one again. Sorry for the formatting issue:

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
Hrsdriving #Subjects Tot.hrsdriving
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 200 1580
12702




"

 

[JRH]

okay, I think I've now become the idiot here... more so then just asking for
help.... :-(

 

[Jim Cone]

JRH,

I don't have a real clue as to how to do this using a formula.
But I think I solved it with VBA code.
The answer I get is 1.06 not the 1.05 you calculated?
You have to be able to plop the following code in a module
and press F5 to get your answer. Post back if you need help there.

Somebody is sure to come along soon with a formula.
'-------------------------------------------------------------------------------
Sub DistributeData()
'Jim Cone - January 21, 2005
'Assumes hours are in cells ("B6:B13")
'Assumes number of subjects are in cells ("C6:C13")
Dim rngOne As Excel.Range
Dim rngTwo As Excel.Range
Dim lngNum As Long
Dim lngCount As Long
Dim lngTotal As Long
Dim lngQty As Long
Dim i As Long
Dim dblAnswer As Double
Dim arrNumbers() As Long

Set rngOne = Range("B6:B13")
Set rngTwo = Range("C6:C13")

'Get total number of subjects driving
lngTotal = WorksheetFunction.Sum(rngTwo)

'Size an array to hold the number of subjects.
ReDim arrNumbers(1 To lngTotal)

'Add the hours from rngOne to the array.
'by looping thru rngTwo and picking the
'hours out of the corresponding cell in rngOne.
For lngQty = 1 To rngTwo.Count
lngNum = rngTwo(lngQty).Value
For lngCount = 1 To lngNum
i = i + 1
arrNumbers(i) = rngOne(lngQty).Value
Next
Next 'lngQty

dblAnswer = Format(WorksheetFunction.StDev(arrNumbers), "###,0.000")
MsgBox "Stdev is " & dblAnswer & " based upon a sampling. ", , _
" Jeff Did It"
dblAnswer = Format(WorksheetFunction.StDevP(arrNumbers), "###,0.000")
MsgBox "Stdev is " & dblAnswer & " based upon the entire population. ", , _
" Jeff Did It"

Set rngOne = Nothing
Set rngTwo = Nothing
End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, USA

 

[Tushar Mehta]

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

 

[Jim Cone]

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

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