Averaging time values in Excel

Topic

New Reply

Excel doesn’t seem to average time values correctly. For example, this group of times, which is really a single column in my Excel spreadsheet:

7:30 7:05 7:53 7:55 7:15 7:15 6:55 7:05 7:15 7:20 7:10 7:00 6:35 7:30 6:55 7:00 7:25 6:45 7:05 6:50 6:25 6:55 7:00 7:20 6:50 6:45 6:50 6:30 6:30 7:15 6:10 7:45 5:30 5:50 6:20 7:00 6:30 7:15 7:35 7:35 7:00 6:40 6:50 7:15 6:55 7:10 7:35 6:35 6:40 6:45 7:45 7:15 7:15 6:30 5:35 7:10 6:40 6:15 7:10 6:45 6:45 7:05 6:30 7:20 6:45 6:00 6:20 7:45 6:15 7:00 7:10 6:35 7:05 7:05 7:45 7:10 7:00 6:45 6:50 7:30 7:35 7:15 7:25 6:30 7:15 6:50 6:50 7:20 6:40 6:10 6:40 6:15 5:35 7:05 6:35 6:45 7:05 6:45 7:00 7:00 6:05 7:25 6:10 6:45 5:30 7:10 7:35 7:20 7:00 7:05 7:20 6:50 7:50 7:48 6:55 7:25 6:55 7:15 7:15 7:20 6:45 7:30 6:45 6:20 6:55 7:05 7:25 6:35 7:05 6:05 7:00 6:45 7:00 7:05 7:10 7:20 6:40 7:40 7:20 6:05 7:15 6:50 7:00 6:50 4:05 8:20 4:00 7:15 7:45 6:30 7:25 6:55 7:15 5:15 7:30 1:10 7:25 7:10 7:25 7:20 7:00 7:25 7:30 7:40 7:30 7:10 7:00 7:00 6:40 7:00 6:35 6:45 7:20 6:00 7:05 2:10 5:40 5:40 7:00 6:30 6:50 7:00 7:15 4:45 6:45 6:35 5:20 6:10 7:30 6:45 6:55 6:40 6:45 6:50 5:50 7:05 7:05 6:50 7:30 7:35 6:40 6:40 6:35 6:50 7:20 7:30 7:10 7:00 7:05 6:50 6:50 7:05 7:20 6:30 7:00 7:20 6:25 6:45 6:35 6:45 6:30 6:50 7:00 7:05 7:00 7:05 6:40 6:00 7:15 7:05 7:10 6:25 7:10 7:15 7:10 7:05 6:55 6:55 7:00 7:15 6:55 7:20 7:55 6:45 7:15 7:30 7:35 7:30 6:45 7:25 7:05 7:00 7:17 7:30 7:05 7:15 6:50 6:35 7:10 6:45 6:50 7:20 7:05 7:20 5:40 7:25 7:40 7:25 7:10 7:15 7:15 6:50 7:30 7:10 7:00 7:10 6:45 6:45 7:45 7:45 7:30 7:25 7:43 7:00 7:20 7:20 7:35 7:15 7:15 7:10 7:10 7:30 7:10 7:30 7:05 7:05 7:35 7:15 7:10 7:25 7:10 6:50 7:25 7:10 6:35 7:30 7:05 7:10 7:05 7:30 7:45 7:15 7:20 8:00 7:10 7:05 7:50 7:00 7:30 6:50 7:00 7:25 7:40 7:30 7:15 7:05 6:55 6:55 7:25 7:25 7:15 7:20 7:10 6:50 7:25 6:45 7:30 7:25 6:35 7:05 7:25 7:35 4:30 7:20 8:00 7:40 7:45 7:25 7:20 7:25 7:55 6:55 7:40 7:15 7:10 6:55 7:20 7:30 7:35 7:35 7:45 7:35 7:25 8:00 7:20

shows an average of 9:17 which doesn’t make any sense!

Any idea what am I doing wrong?

Reply | Quote

Replies

Times and dates are numbers displayed in date format, where the days are whole numbers and the times are fractions. e.g. 1 day = 24 hours = 1440 minutes = 1.
If you average just 2 times, 07:30 & 17:30.
Convert them to minutes: 60×7+30 = 450, 60*17+30 = 1050.
Convert to days: 450/1440 = 0.3125, 1050/1440 = 0.72917
Average: 0.3125+0.72917/2 = 0.52083
Convert to hours: 12:30
All looks good.

It is also possible to have 17:30, 13 January 1970 displayed as just a time, 17:30.
This is a number greater than 1, in this case it’s 25581.72917
Average: 0.3125+25581.72917/2 = 14366.864585
Convert to hours: 20:50
Hmm!

The moral is: check all your dates started life as the same type of number and use 24 hour format for the display.

cheers, Paul

Reply | Quote

=average(A2:A366) gave a result of 6:59

Reply | Quote