Statistical Analysis of Event Duration Times

SteveClark590

New Member
Joined
Jul 5, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 5, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Screenshot 2021-07-07 135247.jpg
    Screenshot 2021-07-07 135247.jpg
    73.4 KB · Views: 6

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'd be interested in how you format your columns to take 12345 and turn it into 1:23:45.

MrExcelPlayground2.xlsm
DEF
15Same as #sTimes
160.0164930560:23:45
170.0120949070:17:25
180.0091782410:13:13
190.0141666670:20:24
200.0298032410:42:55
210.03750:54:00
220.0089351850:12:52
230.0348263890:50:09
240.0188541670:27:09
250.1818518524:21:52Sum
260.0202057610:29:06Average
Sheet40
Cell Formulas
RangeFormula
D16:D26D16=E16
E25E25=SUM(E16:E24)
E26E26=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

New Member
Joined
Jul 5, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 13, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 5, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,840
Messages
5,721,104
Members
422,340
Latest member
canadianbacon357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top