Forum Discussion
pq23or
Apr 08, 2021Copper Contributor
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
pq23or
Apr 08, 2021Copper 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
Apr 08, 2021Iron 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
=MAX(IF(A1:A7>=(18/24),A1:A7,IF(A1:A7<=(6/24),A1:A7+1,"")))
Make a mean of it
- pq23orApr 08, 2021Copper Contributor
hey i attached the workbook in wich actually i need that average truck departure time, i filled up on each day sheet a diffrent time departure and on the WTD sheet F column i need that average time , please have a look , thanks a lot
- SergeiBaklanApr 10, 2021Diamond Contributor
That could be
=AVERAGE( (INDEX(Sun!F:F, MATCH($D7&$E7,Sun!$D:$D&Sun!$E:$E,0))<6/24)+INDEX(Sun!F:F, MATCH($D7&$E7,Sun!$D:$D&Sun!$E:$E,0)), (INDEX(Mon!F:F, MATCH($D7&$E7,Mon!$D:$D&Mon!$E:$E,0))<6/24)+INDEX(Mon!F:F, MATCH($D7&$E7,Mon!$D:$D&Mon!$E:$E,0)), (INDEX(Tue!F:F, MATCH($D7&$E7,Tue!$D:$D&Tue!$E:$E,0))<6/24)+INDEX(Tue!F:F, MATCH($D7&$E7,Tue!$D:$D&Tue!$E:$E,0)), (INDEX(Wed!F:F, MATCH($D7&$E7,Wed!$D:$D&Wed!$E:$E,0))<6/24)+INDEX(Wed!F:F, MATCH($D7&$E7,Wed!$D:$D&Wed!$E:$E,0)), (INDEX(Thur!F:F, MATCH($D7&$E7,Thur!$D:$D&Thur!$E:$E,0))<6/24)+INDEX(Thur!F:F, MATCH($D7&$E7,Thur!$D:$D&Thur!$E:$E,0)), (INDEX(Fri!F:F, MATCH($D7&$E7,Fri!$D:$D&Fri!$E:$E,0))<6/24)+INDEX(Fri!F:F, MATCH($D7&$E7,Fri!$D:$D&Fri!$E:$E,0)) )
- pq23or2210Apr 10, 2021Copper ContributorHi , this is brilliant 👏 thank you , I whould never could figure this out
- pq23orApr 08, 2021Copper ContributorThanks a lot, let you know if it worked