standard deviation of time values

[jafco]

Hello All,
I'm ultimately trying to calculate the standard deviation of a series of elapsed time values, hours and minutes, displayed as total minutes. Here's some samples. My formula for account for before midnight and after midnight time spans is =IF(A2>B2,(B2+1)-A2,B2-A2) I hope my columns are readable.
START TIME STOP TIME ELAPSEDTIME IN MINUTES
23:59 00:15 016
13:30 13:50 020
14:40 15:43 063
15:15 15:17 002
12:00 15:00 180
00:15 01:10 055
22:00 00:01 121
11:03 11:04 001

I used the formula =stdev(C2:C9) which results in 0.044291885 using the general cell format and 0.04429189 using number format to 8 decimal places.
I went out to the web and found a standard deviation calculator and my Excel end result is nowhere near the result of the other calculator.
Is there anyway I can prove this? Can anyone tell me if my end result is correct?

Thanks in advance.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

2​

[/td][td]

23:59​

[/td][td]

0:15​

[/td][td]

16​

[/td][td][/td][td]sdev[/td][/tr]


[tr][td]

3​

[/td][td]

13:30​

[/td][td]

13:50​

[/td][td]

20​

[/td][td][/td][td]

63.78031​

[/td][/tr]


[tr][td]

4​

[/td][td]

14:40​

[/td][td]

15:43​

[/td][td]

63​

[/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]

15:15​

[/td][td]

15:17​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]

12:00​

[/td][td]

15:00​

[/td][td]

180​

[/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]

0:15​

[/td][td]

1:10​

[/td][td]

55​

[/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td]

22:00​

[/td][td]

0:01​

[/td][td]

121​

[/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td]

11:03​

[/td][td]

11:04​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[/table]

In C2 enter and copy down:

=(B2-A2+(B2 < A2))*24*60

In E2 enter:

=STDEV(K2:K9)

 

[DRSteele]

I got nothing like .0443 for stdev. Try this:

	A	B	C
in	out	duration
mean
st.dev.p
st.dev.s

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]23:59[/TD]
[TD="align: right"]0:15[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:16[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]13:50[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:20[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]14:40[/TD]
[TD="align: right"]15:43[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1:03[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]15:15[/TD]
[TD="align: right"]15:17[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:02[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3:00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0:15[/TD]
[TD="align: right"]1:10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:55[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]0:01[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2:01[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]11:03[/TD]
[TD="align: right"]11:04[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0:01[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]48.0625[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]59.66102[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]63.78031[/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=(B2+(A2>B2)-A2)*24*60[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=AVEDEV(G2:G9)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]=STDEV.P(G2:G9)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]=STDEV.S(G2:G9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jafco]

Hi Aladin, Thanks for the response. Your suggestion worked however in changing my col C cell format I lost the Total Time in hours and minutes that I had set up. I didn't mention it in the original post because I didn't think it was relevant to the question. So I need col C to be expressed in minutes. My total time is just a simple =sum(c2:c9) but the cell format is expressed in hh:mm.

 

[jafco]

Hi DRSteele,
Thanks for the response. Your suggestion worked but I need my col C cells to be formatted to be expressed in minutes because I have a Total Time Elapsed calculation that is simply a sum
=sum(C2:C9). if I change the Col C cell format I loose that total time.

 

[Aladin Akyurek]

Hi Aladin, Thanks for the response. Your suggestion worked however in changing my col C cell format I lost the Total Time in hours and minutes that I had set up. I didn't mention it in the original post because I didn't think it was relevant to the question. So I need col C to be expressed in minutes. My total time is just a simple =sum(c2:c9) but the cell format is expressed in hh:mm.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

2​

[/td][td]

23:59​

[/td][td]

0:15​

[/td][td]

0:16​

[/td][td][/td][td]sdev[/td][/tr]


[tr][td]

3​

[/td][td]

13:30​

[/td][td]

13:50​

[/td][td]

0:20​

[/td][td][/td][td]

1:03​

[/td][/tr]


[tr][td]

4​

[/td][td]

14:40​

[/td][td]

15:43​

[/td][td]

1:03​

[/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]

15:15​

[/td][td]

15:17​

[/td][td]

0:02​

[/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]

12:00​

[/td][td]

15:00​

[/td][td]

3:00​

[/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]

0:15​

[/td][td]

1:10​

[/td][td]

0:55​

[/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td]

22:00​

[/td][td]

0:01​

[/td][td]

2:01​

[/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td]

11:03​

[/td][td]

11:04​

[/td][td]

0:01​

[/td][td][/td][td][/td][/tr]
[/table]

C2, copied down:

=(B2-A2+(B2 < A2))

E2:

=STDEV(C2:C9)

Custom format E2 as:

h:mm

 

[jafco]

oh crap man!! I had it all along...all I had to do was change the cell format to time!! How stupid am I? When I take my original result 0.044291885 and change the format I come up with the 1:03 as in your example. I really didn't have to change anything else in my original worksheet.

 

[Aladin Akyurek]

oh crap man!! I had it all along...all I had to do was change the cell format to time!! How stupid am I? When I take my original result 0.044291885 and change the format I come up with the 1:03 as in your example. I really didn't have to change anything else in my original worksheet.

Indeed. I replied because of those strange values you posted in column C... like 016, 020, etc...

 

[jafco]

This whole thing is set up for a bunch of testers. They need to record time elapsed/test cast and expressed in minutes. Each test case will be well under an hour but I just used the numbers that I did to show how everything works. However in the end they need to know the total time so that planned manhours can be compared with actual manhours. The standard deviation is just something new that they came up with. I thank you and DRSteele again for your assistance.