How to find the average of difference between two columns?

Question

Imagine you have a Google sheet with two columns, each holding a time - let's say one is bus departure and the other is bus arrival.

Is there any elegant way to present the average of all the time the bus spent on the way each day?

Example:

Departure     Arrival     Average_Time_in_Transit
13:00         14:00       1:00
13:15         14:15
12:15         13:15
12:00         13:00

Per row, I think - How much time the bus spent traveling, on average, every day? If it traveled for exactly one hour every day the result should be one cell holding 1:00 — Mike Meyers, Commented May 2, 2019 at 9:23

user0 · Accepted Answer · 2019-05-02 09:55:08Z

1

=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "[h]:mm"), ))

=TEXT(AVERAGE(ARRAYFORMULA(IF(LEN(A2:A), B2:B-A2:A, ))), "[h]:mm:ss")

=ARRAYFORMULA(IF(LEN(A2:A), TEXT((B2:B-A2:A)/COUNTA(A2:A), "[h]:mm:ss"), ))

answered May 2, 2019 at 9:29

user0

27.1k10 gold badges53 silver badges78 bronze badges

This is awesome, thank you! Is there any way you know of to do this without creating a third column "C" of the daily time difference?
– Mike Meyers
Commented May 2, 2019 at 9:44
@MikeMeyers did you see the 2nd picture? (if you use 2nd formula, you dont need column C with differences at all)
– user0
Commented May 2, 2019 at 9:45
1

I have actually missed it, and it is exactly what I was looking for! Thanks, @user(), you're awesome!
– Mike Meyers
Commented May 2, 2019 at 9:53
@MikeMeyers I also added a breakdown of the average per each row
– user0
Commented May 2, 2019 at 9:55

Add a comment |

1 Answer 1