How to calculate individual values as percentages of group averages in Google Sheets QUERY

Other Questions Google Sheets
1

I need some assistance with a Google Sheets formula that can help me see how each row relates to the average of its group. Currently, my data looks like this:

player   score   level   points   type     group_id
max      800     12      900      attack   abc123
john     600     11      700      attack   abc123
kate     500     10      600      attack   abc123
tom      400     8       500      attack   abc123

I have managed to calculate group averages using this formula:

=Query(A:F, "select F, avg(B), avg(C), avg(D) where F is not null and E='attack' group by F",-1)

This results in:

group_id    avg_score   avg_level   avg_points
abc123      575.00      10.25       675.00

However, I am looking to show each row with extra columns that indicate the percentage of each score relative to its group average. For instance, max’s score of 800 should appear as 139%, calculated by 800/575 = 1.39. Can I adjust my QUERY formula to do this, or is a different method necessary?

2

You can accomplish this by combining your QUERY results with lookups from your original dataset. Start by calculating group averages with your QUERY function, then use VLOOKUP or INDEX/MATCH to pull those averages into your main data. For example, use =B2/VLOOKUP(F2,group_avg_range,2,FALSE) to compute the percentage relative to the group average. Alternatively, an ARRAYFORMULA together with SUMIF and COUNTIF can provide a dynamic setup, but generally, the lookup method is simpler and more efficient for this type of analysis.

3

There’s actually an easier way - just add a calculated column directly in your query. Try something like =Query(A:F, "select player, score, score/avg(score) where group_id='abc123' and type='attack' group by player, score") but I’m not totally sure about the syntax, might need some tweaking