How to calculate average spending time by using VBA?

[AngelicaWan]

How to use VBA to calculate the average time of this list. And how to calculate the difference between each time and average time?

Thanks a lot!

Like there's a list of time:
[TABLE="width: 48"]


<colgroup><col style="width: 48pt;" width="64">
<tbody>[TR]

[TD="class: xl65, width: 64, bgcolor: transparent"]00:01:28[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:01:23[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:01:01[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:05:07[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:02:45[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:03:14[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:01:14[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:44[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:46[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:01:19[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:12[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:37[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:52[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:34[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:02:16[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"]00:01:28

[/TD]

[/TR]


</tbody>[/TABLE]

 

[nicknep]

Well you could do that with just an excel formula: =AVERAGE(A1:A22)

However, if you're feeling like you'd prefer VBA, I'm sure this would work:

Sub averagetime()


Range("A23") = WorksheetFunction.Average(Range("A1:A22"))


End Sub

 

[AngelicaWan]

Thank you nicknep!

I was failed when I tried at the first time. Then I reliazed I may write it under module not under particular sheet.

 

[nicknep]

Hey, no problem! I just now read the second part of your question and worked out something for that too:

Sub AverageTime()
Cells(1, 1).Select
lr = Selection.End(xlDown).Row 'finds last row
Cells(lr + 1, 1) = WorksheetFunction.Average(Range("A1:A" & lr))
For x = 1 To lr
If Cells(x, 1) > Cells(lr + 1, 1) Then Cells(x, 2) = Cells(x, 1) - Cells(lr + 1, 1)
If Cells(x, 1) < Cells(lr + 1, 1) Then Cells(x, 2) = Cells(lr + 1, 1) - Cells(x, 1)
Next x
End Sub

Assuming your data starts in cell A1 and is in column A, this macro finds the last row of your data and averages all the times. It then takes the difference of the average and each time and outputs it in the adjacent cell. I ran into a small problem where a time can't be negative, so it first checks to see which time is larger, then subtracts by that first.

I hope this helps!

 

[AngelicaWan]

Thank you so much nicknep! Awesome! That is a great help!

I just noticed the reply and I also realized the one with the negative difference. So I marked them with different font color.

However new issue comes. I couldn't successfully run the codes with the following number list. I made the format as [h]:mm:ss and that still does not work. So how to do with the ones larger than 12:00:00?

[TABLE="width: 48"]


<colgroup><col style="width: 48pt;" width="64">
<tbody>[TR]

[TD="class: xl63, width: 64, bgcolor: #DDEBF7"]02:38:47[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]09:00:56[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: #DDEBF7"]14:02:04[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]08:06:19[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]05:30:08[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]06:34:24[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]04:03:56[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]06:40:55[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]13:04:09[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]00:22:32[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]03:17:23[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]06:22:27[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]07:17:58[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]04:14:10[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]07:22:32[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]02:27:45[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]03:11:29[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]10:50:05[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]07:07:58[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]08:33:04[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]00:00:00[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]00:07:27[/TD]

[/TR]

[TR]

[TD="class: xl64, bgcolor: transparent"]03:39:37[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: #DDEBF7"]04:15:26[/TD]

[/TR]


</tbody>[/TABLE]


Thank you!

 

[AngelicaWan]

Oh, I know a little trick here. When the time is larger than 12:00:00, you can copy the data to a word document with value only format. Then copy back to spreadsheet is OK.

But I still don't know what to do with the ones like:

[TABLE="width: 143"]
<tbody>[TR]
[TD] 1:01:20:27[/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 143"]
<tbody>[TR]
[TD]1:04:04:02[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]