Google Sheets: averaging duration data per weekday

Question

I've got date/ duration data, e.g.:

16-Apr  (Mon)  30mins

16-Apr  (Mon)  90mins

17 Apr (Tue) 60 mins

19-Apr (thu) 15 mins 

19-Apr (thu) 20 mins 

19-Apr (thu) 20 mins

21-April (Sat ) 120 mins  

23-April (Mon) 60 mins 

24-Apr (Tue)15 mins

I want to produce an average duration per weekday

A pivot table with date can give me a sum of the durations for each date.

A pivot table on (derived) Day of the week gives me an average for the number of entries for that day of the week, e.g. in the above, Monday = 3 entries and I want an average from 2 (dates).

What I want is Mondays = x mins, Tues= y mins, Wed =z mins, etc.

Can this be done in one step with a pivot table or array formula?

Answer 1

Suppose you have your dates in column E, and durations in F

This formula will give you a summary by day of week:

=query({arrayformula(weekday(E2:E7)), arrayformula(n(F2:F7))},
       "select Col1, sum(Col2) group by Col1")

To get an average, use avg(Col2) instead of sum(Col2). You still have to format the second result column as Duration, and find a way to convert the days of week from numeric to text, but that can be left as an exercise to the reader :-)

Answer 2

You could do this to add labelling

=ArrayFormula(query({hlookup(weekday(A:A),{1,2,3,4,5,6,7;"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},2,false),B:B},"select Col1,avg(Col2) group by Col1 label Col1 'Day'"))

The day numbers in column C are just for checking.

If any of the dates are missing, you need to ignore them otherwise they will all be treated as Saturdays...

=ArrayFormula(query({A:A,hlookup(weekday(A:A),{1,2,3,4,5,6,7;"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},2,false),B:B},"select Col2,avg(Col3) where Col1 is not null group by Col2 label Col2 'Day'"))

EDIT

OK this is an answer for the actual requirement which is for sum of time durations for each weekday but divided by number of unique dates which fall on that weekday.

=ArrayFormula(query({query({C2:C,weekday(C2:C),choose(weekday(C2:C),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),E2:E},"select Col3,sum(Col4) where Col1 is not null group by Col2,Col3"),
query({unique(C2:C),weekday(unique(C2:C))},"select count(Col2) where Col1 is not null group by Col2")},"Select Col1,Col2/Col3 label Col1 'Day',Col2/Col3 'Average'"))

Notes

(1) The number of groups based on unique dates is exactly the same as the number of groups based on dates with duplicates.

(2) To get the day names in the correct order (Sunday, Monday, Tuesday...) I grouped on weekday number (1-7) then weekday name (Col2,Col3 in the first inner query). This doesn't create any more groups than just using Col3, but has the effect of putting the days in weekday number order instead of weekday name (alphabetical) order while still allowing you to put Col3 (weekday name) in the select list.

(3) Have included @ttarchala's recommendation of using Choose rather than Hlookup.