How may I add an average count of rows by weekday to this example Google Sheets

Question

Here is an example Google Sheet that summarizes Col A, D by weekday totals and counts.

I would like to add an Average number of items by Weekday, like K9 L9 but show it in formula K1.

There are two(2) Mondays and one had 10 rows, the other 2 so the average is 6 for Mondays so far.

https://docs.google.com/spreadsheets/d/1VvJOtnG1wyIjAIiQUerwqZLuTmDs3RryyGwLsLBwSZo/edit?usp=sharing

This is the formula in K1:

=QUERY(Query(A1:D, "select (A), sum(D) 
                    where A is not null and dayOfWeek(A)<>1
                    group by (A) 
                    order by  dayOfWeek(A) "),
              "select dayofWeek(Col1), Count(Col1), Sum(Col2)
               group by dayofWeek(Col1) 
               label dayOfWeek(Col1) 'Weekday', sum(Col2) 'Total', count(Col1) '#' 
               format dayOfWeek(Col1) 'dddd', sum(Col2) '###0.' ")

Answer 1

Try this formula

=QUERY({ QUERY( Query(A1:D, "select (A), sum(D) where A is not null and dayOfWeek(A)<>1 group by (A) order by dayOfWeek(A) "), "select dayofWeek(Col1), Count(Col1), Sum(Col2) group by dayofWeek(Col1) label dayOfWeek(Col1) 'Weekday', sum(Col2) 'Total', Count(Col1) '#' format dayOfWeek(Col1) 'dddd', sum(Col2) '###0.' "), QUERY({QUERY({A1:D},"select dayofweek(Col1)")},"select Col1, count(Col1) where Col1 is not null group by Col1 ")},"select Col1, Col2, Col3, (Col5/Col2) label (Col5/Col2) 'Avg' ")

This formula is the same as yours with the addition of an extra query that:

• calculates a total count of dayofweek -> count(Col1)
• divides the count by the count in the # field.

---

Output