Search code examples
dategoogle-sheetsgoogle-sheets-formulaaverage

How to Average by day of week in Google Sheets?

Given a table of dates and values.

Dates

How to average by day of week?

enter image description here

I found AVERAGEIF to average and TEXT(E4, "dddd") to convert to day of week.

But how to combine those two functions?

Date Value
1/1/2001 1
1/2/2001 2
1/3/2001 3
1/4/2001 3
1/5/2001 6
1/6/2001 3
Day of Week Average
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Solution

  • Go with QUERY() function. Try-

    =QUERY({INDEX(TEXT(A1:A15,"dddd")),B1:B15},
"select Col1, avg(Col2) group by Col1 label Col1 'Day', avg(Col2) 'Average'")

    To make it dynamic, use-

    =QUERY({INDEX(TEXT(TOCOL(A1:A,1),"dddd")),TOCOL(B1:B,1)},
"select Col1, avg(Col2) group by Col1 label Col1 'Day', avg(Col2) 'Average'")

    enter image description here