Forum Discussion

pq23or's avatar
pq23or
Copper Contributor
Apr 08, 2021

time average in excel??

hi, im trying to average times in format hh:mm, for example

22:00

21:30

03:00

00:00

so i get average between the smallest and latest value?

any help thanks

 

13 Replies

  • When working with times that span midnight, you should include the dates:
    3-May-2021 22:00
    3-May-2021 21:30
    4-May-2021 03:00
    4-May-2021 00:00

    The average of these is 3-May-2021 23:37. You can format the cell with the average formula to just show the time using a number format of hh:mm, which keeps the date in the cell. Or you can adjust the formula to:
    =AVERAGE(A1:A4)-INT(AVERAGE(A1:A4))
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    pq23or 

    It very depends on context. Do you have hundreds of such times in sequence, when how to recognize is that same or next date. Or, in opposite, if we have only couple of times, 6:00 and 18:00 how do we know 6:00 is first time at the same date or later time on next date.

    • pq23or's avatar
      pq23or
      Copper Contributor
      Hi, bassically i have 7 of these times and i need an average on that format hh:mm, those times are departure times starting with 18:00pm to next day 6:00am so any (hour: minutes) between this interval of time( and only inside this interval) , thanks a lot for help
      • MindreVetande's avatar
        MindreVetande
        Iron Contributor
        =MIN(IF(A1:A7>=(18/24),A1:A7,IF(A1:A7<=(6/24),A1:A7+1,"")))
        =MAX(IF(A1:A7>=(18/24),A1:A7,IF(A1:A7<=(6/24),A1:A7+1,"")))
        Make a mean of it
  • You mean the smallest and largest value?
    =Mean(min(a1:a4),Max(a1:a4))
    or
    =(min(a1:a4)+Max(a1:a4))/2
    • pq23or's avatar
      pq23or
      Copper Contributor
      hi, well in the example 22:00PM would be smallest value and largest would be 3:00AM
      • MindreVetande's avatar
        MindreVetande
        Iron Contributor

        pq23or 

        Ok, 22:00 is the first time so it might be the lowest. But if it's in a chronologically order 00:00 comes after 03:00 so it should be the last.

        I simply don't get the pattern. Is 00:00 an empty value that you want to exclude?

        This takes the mean from the first cell in column A and the last cell with a value that is not 0

        =mean(A1,INDEX(A:A,MAX(Row(A:A)*(A:A<>0))))

Resources