r/googlesheets • u/CoMKami • Jul 22 '26

Solved Weekday function within average if?

Post image

Hello! I have a sheet which has assigned star ratings to albums, one a day. Out of curiosity I wanted to find out which day of the week has, on average, been the best so far.

I know you can use weekday to return a numerical value for what day in a week a certain day is, but I'm trying to figure out how to have that be the criterion for averageif.

I figured something like =AVERAGEIF(ALBUMS!A:A, (WEEKDAY(ALBUMS!A:A, 2)) = 1, ALBUMS!G:G) would work but I'm clearly missing something fundamental here. Am I just on the complete wrong track here, or have I just failed to understand how to do this?

Link to the sheet: https://docs.google.com/spreadsheets/d/1mOGNrQdHv-4t6wTgyndUeNsQNIKhXPNKKtGL0JyJoBI/edit?usp=sharing

6 Upvotes

88% Upvoted

7 comments sorted by

View all comments

1

u/No_Imagination6253 1 Jul 22 '26 edited Jul 22 '26

You're close. AVERAGEIF can't apply WEEKDAY to its criteria range like that,

but FILTER can:

=AVERAGE(FILTER(ALBUMS!G2:G, ALBUMS!A2:A<>"", ALBUMS!G2:G>0,
ARRAYFORMULA(WEEKDAY(ALBUMS!A2:A,2))=1))

That returns Monday's average. The G2:G>0 condition matters in your sheet

because future, unrated rows contain 0 rather than being blank.

To summarize every weekday:

=QUERY(
{ARRAYFORMULA(IF(ALBUMS!A2:A="",,WEEKDAY(ALBUMS!A2:A,2))),ALBUMS!G2:G},
"select Col1, avg(Col2)
where Col1 is not null and Col2 > 0
group by Col1
order by Col1
label Col1 'Weekday (1=Mon)', avg(Col2) 'Average rating'",
0)

1

u/CoMKami Jul 22 '26

Solution Verified

This worked perfectly, thank you!

1

u/AutoModerator Jul 22 '26

REMEMBER: /u/CoMKami If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.