How to calculate average scores based on player names across multiple columns in Google Sheets?

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need help with a tricky calculation. I want to find the average score for each player, but the players’ names can appear in different columns within the same row. Here’s what I’m dealing with:

| Score | Player 1 | Player 2 | Player 3 |
|-------|----------|----------|----------|
|   3   |  Kylie   |   Anna   |          |
|   4   |   Anna   |   Lois   | Michelle |
|   5   | Michelle |          |          |

I’m trying to get this result:

| Player  | Times Played | Avg Score |
|---------|--------------|-----------|
|  Anna   |      2       |    3.5    |
|  Kylie  |      1       |     3     |
|  Lois   |      1       |     4     |
| Michelle|      2       |    4.5    |

I’ve managed to list the unique players and count their appearances, but I’m stuck on the average score part. My AVERAGEIF formula isn’t working as expected. It’s either picking only one score or returning errors. Any ideas on how to solve this?

2

hey there! i’ve dealt with similar stuff before. have u tried using ARRAYFORMULA with VLOOKUP? it can handle players in different columns. something like:

=ARRAYFORMULA(VLOOKUP(unique players, {all player columns, scores}, 2, 0))

just adjust the ranges. hope this helps!

3

I’ve encountered a similar challenge in my scorekeeper role for a local sports league. Here’s a solution that should work for you:

Use the QUERY function combined with TRANSPOSE to reshape your data, then apply QUERY again for the final result. Here’s the formula:

=QUERY(QUERY(TRANSPOSE(
{A2:A, ROW(A2:A), B2:D, ROW(B2:D), ROW(B2:D)}),
“SELECT Col1, Col2, Col3 WHERE Col3 IS NOT NULL”),
“SELECT Col3, COUNT(Col3), AVG(Col2) GROUP BY Col3 LABEL COUNT(Col3) ‘Times Played’, AVG(Col2) ‘Avg Score’”)

This approach first flattens your data, then calculates the averages and counts. It’s efficient and handles varying player positions well. Adjust the ranges (A2:A, B2:D) to match your actual data set.