Given a table of dates and values.
How to average by day of week?
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 |
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'")
Sunday to Saturday in cells. Lets say put Sunday to D2 cell and down then use INDEX(AVERAGEIFS($B$1:$B,TEXT($A$1:$A,"dddd"),D2)) formula to get average.=LAMBDA(wk,{wk,MAP(wk,LAMBDA(x,AVERAGEIFS(B1:B,INDEX(TEXT(A1:A,"dddd")),x)))})({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}).INDEX() is equivalent of ArrayFormula().
=QUERY(QUERY({INDEX(TEXT(TOCOL(A1:A,1),"dddd")),TOCOL(B1:B,1),INDEX(WEEKDAY(TOCOL(A1:A,1)))}, "select Col1, avg(Col2), Col3 group by Col1, Col3 order by Col3 label Col1 'Day', avg(Col2) 'Average'"),"select Col1, Col2")
HSTACK()function.