Statistical Analysis of Event Duration Times

[SteveClark590]

I have perused many answers to this question on this forum and other forums to no avail. I decided this forum is my best option, so here goes.

I am accumulating time duration data of a repeated event (task). Different test subjects perform the task at different speeds under varying conditions. I need to be able to analyze the data relevant to those conditions. I have been able to set the format of the columns to take a simple number (ex. 12345) and have the entry read as a hh:mm:ss time quantity (ex. 01:23:45). However, when I try to perform data analysis things get weird. I understand that Excel converts time entries to decimal-based numbers to perform functions and then is supposed to convert them back to hh:mm:ss form, but that is not happening.

The sum totals are showing up with minutes and seconds values above :59 which makes the corresponding minutes and hours values off by one.

How do I format the sums to read with a max of :59 seconds and minutes?

Excel will calculate the average (mean) of a set of data just fine because the data entries never have a value greater than :59. Not sure if this will be a problem, but how do I maintain the ≤:59 format while performing statistical analyses to find mode and median values?

Thank you for any and all help.

 

[JamesCanale]

An example of what you are trying to do would be great. especially if you use the xl2BB addin that you get from this site.

 

[SteveClark590]

Thank you. I don't know anything about the xl2BB addin. But here is a screenshot of what I get when I select the hh:mm:ss data above the yellow.

 

[JamesCanale]

I'd be interested in how you format your columns to take 12345 and turn it into 1:23:45.

MrExcelPlayground2.xlsm
	D	E	F
15	Same as #s	Times
16	0.016493056	0:23:45
17	0.012094907	0:17:25
18	0.009178241	0:13:13
19	0.014166667	0:20:24
20	0.029803241	0:42:55
21	0.0375	0:54:00
22	0.008935185	0:12:52
23	0.034826389	0:50:09
24	0.018854167	0:27:09
25	0.181851852	4:21:52	Sum
26	0.020205761	0:29:06	Average
Sheet40

Cell Formulas
Range	Formula
D16:D26	D16	=E16
E25	E25	=SUM(E16:E24)
E26	E26	=AVERAGE(E16:E24)

This is how I'm looking at your data (the xl2bb is super helpful - it's an addin you can get from this site). To the left, I'm showing the number that the time is stored as

 

[SteveClark590]

Okay. Thanks. Doesn't work on my EXCEL. I have the same formulas, but the results don't change from what I posted earlier.
??? Someone told me that CSV is a better format for working with time data. Is that true?

 

[JamesCanale]

I guess that CSVs will store time as whatever format it's shown in. Excel stores time as just a number - where "1" is a full day, and hours are 1/24, and Now is something like 44386.3 (days since 1/1/1900).

I figure that your excel is showing something that looks like time, but it isn't to excel. What exactly you have there is important here (excel usually doesn't have two leading zeroes for hours in time). What if you reformat one of the 'times' you have there as a number? What does it say?

 

[SteveClark590]

I guess that CSVs will store time as whatever format it's shown in. Excel stores time as just a number - where "1" is a full day, and hours are 1/24, and Now is something like 44386.3 (days since 1/1/1900).

I figure that your excel is showing something that looks like time, but it isn't to excel. What exactly you have there is important here (excel usually doesn't have two leading zeroes for hours in time). What if you reformat one of the 'times' you have there as a number? What does it say?

Thank you, James. I’ll keep trying stuff. If I get a solution I’ll post back here. Don’t wait up.