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
"JRH" <jrh wrote in message
...
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.
# of sub = subjects
x f xf x2f
Hrsdriving #Sub Tot.hrs
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
|