24 hr time average (value return)

[stoke]

hi, i have the following data (showing delivery times) in 24 hr format

00:15
23:50
00:01
23:25

i want the average of these delivery times, within this window of, in this instance, 50 mins rather than over a whole 24 hr period - but excel calculates the average delivery time as 11:52 hrs!

at the moment i am adding 24 to the time after midinight to make the result 'real'

any help would be very much appreciated!

 

[Zathras]

So, the first delivery took 15 minutes, the second 1,430 minutes, the third only one minute and the fourth 1,405 minutes. The average of those four delivery times is 712.75 minutes which equals 11.87917 hours (expressed as 11:52 in hours & minutes).

What is the problem?

On the other hand, what is the average of July 4, September 6, October 21, and April 15?

 

[stoke]

sorry i didn't explain myself very well.
This is not about how long each delivery took, but arrival time at destination.
for example: over four days i arrive at same destiantion at the following times:
00:15
23:50
00:01
23:25
The real average arrival time here is 23:50hrs. (as opposed to 11:52 hrs which is not correct).
how do i get excel to return this value?

Thank you!

 

[Zathras]

Excel stores dates and times as a decimal number. The portion to the left of the decimal point is the number of days since 1900 (or 1904, depending on the setting) and the portion to the right of the decimal point represents time as a fractional part of a 24 hour period.

With that in mind, what you are doing by adding 24 to the post-midnight times is on the right track. But what you really need to add is just 1, representing the next day.

The simplest way may be just to include the date along with the time. For example:

A8: 4/20/03 0:15
A9: 4/19/03 23:50
A10: 4/20/03 0:01
A11: 4/19/03 23:52
A12: =AVERAGE(A8:A11)

displays 4/19/03 23:52 in A12.

To experiment further, try setting the formats to General to see what date/times really look like internally to Excel.

 

[stoke]

ok - thanks very much for your help!