Forum Discussion
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)) 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.
- 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
- =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- hi, well in the example 22:00PM would be smallest value and largest would be 3:00AM
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))))
