Silly question about averaging times in excel

Howdy!

I'm doing a study for my job where I had people track how long it takes them to do certain tasks each day. I set up a macro to record a start and end time, and then put a cell on the worksheet they were using to subtract the start time from the end time. Here's an example since I can't share the actual worksheet:

Start Time	End Time	Total Time
10:31 am	10:37 am	06:00
10:55 am	11:05 am	10:00
11:07 am	11:15 am	08:00

So you can see, it's nothing too fancy. Because of the scope of the project, there are some people who had upwards of 1k+ lines of data to track. So!
What I did was make a formula that added up the times by task.

Here's an example:

Task Name	Total Time
Sweeping	01:51:01

So there's 1 hour, 51 minutes, and 1 second worth of task time after adding it all up. Then, I try to divide by the number of times the task was performed (40, in this instance), and that's when things get kooky.

The actual answer should be somewhere in the neighborhood of 2 minutes and change, but no matter what I try (formatting as [h]:mm, mm:ss, formatting as a number, formatting as [m]:ss, multiplying various items by 1440, dividing by 60, etc), I never get the right answer. I usually get 14:47, which is 12 minutes too high.

So the super short version of this question would be, how can I take a total sum of time ranges for tasks, then divide that sum by the number of times the tasks were performed to get the correct answer?

It's probably something comically easy that I'm missing, but 2 hours in and I'm ready to throw something. Any help would be appreciated!

Have you tried using AVERAGEIFS() ?

BSB

Originally Posted by BadlySpelledBuoy

Have you tried using AVERAGEIFS() ?

BSB

Yup! That was what I tried first, actually. Here's a screenshot for reference (using AVERAGEIFS)
Capture1.JPG

The formula up top is:

Please Login or Register  to view this content.

If I take that result (19:14:47) and divide it by the next cell (40), it's closer to the correct answer, but still wrong.
It spits out a result of a 4:52 average, but I know for a fact it's 2 minutes and 50 some odd seconds. So I'm not sure where I'm gumming up the works.

I cannot replicate the issue you're seeing. If I divide 1:51:01 by 40 I get 0:02:47, whichever way I do it.
I think we are going to need to see the workbook. Desensitized to protect sensitive data.

BSB

Marvin, that PivotTable looks like it'd do what I want, but sadly I have 0 experience with PivotTables. I should probably remedy that...

BSB, attached is a dummy worksheet. Same error, 4:52 in the highlighted blue area under AL.

I have a strong feeling I'm gonna feel like an idiot when it gets pointed out what I'm doing wrong...

Originally Posted by FDibbins

I think I have found the problem. Many of your times also include a date - look at C6 for instance. This results in C6-B6 being 42471.0022800926 instead of 0.00228009259412326

Try this fix for the calc...
=MOD(C1,1)-MOD(B1,1)

Then use this for the calcs...
=AVERAGEIFS($D$1:$D$945,$A$1:$A$945,F1)

It worked! Thank you so much, that was amazing! Rep for you good sir!