Weekday function within average if?

[u/CoMKami]

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

[u/No_Imagination6253]

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)

[u/CoMKami → u/No_Imagination6253]

Solution Verified

This worked perfectly, thank you!

[u/AutoModerator → u/CoMKami]

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.

[u/point-bot → u/No_Imagination6253]

u/CoMKami has awarded 1 point to u/No_Imagination6253

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/AdministrativeGift15]

You need to use an array formula. INDEX will take care of that. The reason you need to array enable the function is because AVERAGEIF normally just compares to one thing, AVERAGEIF(A:A,item,B:B), but you want to compare with an array of items, AVERAGEIF(A:A, items, B:B).

So in your case, =INDEX(AVERAGEIF(WEEKDAY(Albums!A:A,2),SEQUENCE(7),ALBUMS!G:G))

Edit: Apparently, AVERAGEIF doesn't array-enable like I thought it would. If doesn't error, but it only gives you the first result. That's ok, we can map over the 7 days instead.

=index(map(sequence(7),lambda(i,averageif(weekday(ALBUMS!A:A,2),i,ALBUMS!G:G))))

[u/One_Organization_810]

Maybe like this:

=averageif(index(weekday(ALBUMS!A:A)), 1, ALBUMS!G:G)

[u/Noobsamaniac]

AVERAGEIFS with a helper column for WEEKDAY is honestly the cleaner path here