Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MEAN in Military Time
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MEAN in Military Time
On Apr 3, 3:02*pm, CR3 wrote:
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MEAN in Military Time
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 "Dave F" wrote: On Apr 3, 3:02 pm, CR3 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MEAN in Military Time
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) -- Biff Microsoft Excel MVP "CR3" wrote in message ... 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 "Dave F" wrote: On Apr 3, 3:02 pm, CR3 wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
MEAN in Military Time
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 CR3 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
Converting Standard Time into Military Time | Excel Discussion (Misc queries) | |||
How to keep 0 for military time | Excel Discussion (Misc queries) | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions |