Results 1 to 3 of 3
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    968
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Averaging time values in Excel

    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?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,202
    Thanks
    49
    Thanked 987 Times in 917 Posts
    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: 60x7+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

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    =average(A2:A366) gave a result of 6:59

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •