Averaging 24hr times

[nwppp1]

Hi Guys,

I am having some trouble getting an average time when the times are across a 24hr period. For instance if these were the following times of medication consumption, the early AM times bring the average way down as if the later PM times were later on the same day. However, I need to calculate the average as if the AM time (0:25am) actually represent 24:25pm in order to get an accurate average:

A
23:45
22:50
23:05
0:25
22:30

Any ideas, other than converting the PM to AM and AM to PM, calculating an average, converting the average time back to PM after calculating the average?

Cheers.

 

[AlphaFrog]

Are there dates associated with each time? Otherwise, how do you know if 0:25 is 12:00 AM of the current day or 12:00 AM of the next day?

Or is it that all the PM times are the current day and all the AM times are the next day?

 

[nwppp1]

There are dates associated within the database. However, in this given situation all PM times are current day and AM times are next day.

 

[AlphaFrog]

There are dates associated within the database. However, in this given situation all PM times are current day and AM times are next day.

Try something like this...
=(SUM(A:A)+COUNTIF(A:A,"<0.5"))/COUNT(A:A)