Home |
Search |
Today's Posts |
#1
|
|||
|
|||
STDEV...HELP
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 |
#2
|
|||
|
|||
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 " |
#3
|
|||
|
|||
okay, I think I've now become the idiot here... more so then just asking for
help.... :-( "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 " |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|