MEAN in Military Time

[CR3]

I have the following military times in a column.

0:10
1:00
22:05
23:05
23:05


I am trying to take the AVERAGE of the times. I am getting 13:53 as the
average time. However, this is incorrect. The actual average should be
23:45 in military time (corresponding to 11:45 pm.) How do I come up with
this in Excel?

Thank You.

CR3

 

[Dave F]

I have the following military times in a column.

0:10
1:00
22:05
23:05
23:05

I am trying to take the AVERAGE of the times.  I am getting 13:53 as the
average time.  However, this is incorrect.  The actual average should be
23:45 in military time (corresponding to 11:45 pm.)  How do I come up with
this in Excel?

Thank You.  

CR3

Multiply all these values by 24 to see how many 24ths of a day they
comprise. Average those products and you'll get your answer.

I get an average of 9:12 PM, not 11:45 PM.

Dave

 

[CR3]

this still is not right. The numbers shown below are equal to:

12:10am
1:00am
10:05pm
11:05pm
11:05pm

These are times at which a shipment goes out for one route over a five day
time frame. The average of these needs to equal a number between the min.
(10:05 pm) and the max (1:00 am). Which should be 11:45pm. I would not have
an average of 9:45pm because I could never ship out that early.

Thank You.

CR3

 

[T. Valko]

I get an average (based on your logic) of 23:29.

To get that I had to add 24 hrs to 12:10 AM and 1:00 AM.

You are basing your logic on the max time is defined as a time that rolls
over past midnight into the *next day*.

So, the only way to do this is to define a max shipping time and add 24 hrs
to those times. In other words, if your "shipping day" starts at 9:00 PM and
ends at 2:00 AM then for each time past midnight you'd have to add 24 hrs.

With your times in A1:A5 and using 2:00 AM as the max shipping time this
array formula** returns 23:29 -

=AVERAGE(IF(A1:A5<=TIME(2,0,0),1+A1:A5,A1:A5))

Format as time 13:30

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Bob I]

It is perfectly correct. Adding the listed times, you have a total of
69.42 hours and if you divide that by 5 you get 13.884 hours and if you
convert that to Hours:minutes you have 13:53