I need to collect the average of a column by referencing name in another column

Lindsay AR

I have columns in a sheet per the screen below. The Team Member is input through a form submission and can potentially be input many times. I am looking for a formula to collect the average score from all rows where one name appears without having to say ="Scooby Doo".

Lindsay AR

Update: I just exported to Power BI and did all my calculations there.

L_123

=avg(collect([% max score]:[% max score],[team member]:[team member],not(contains(" ",@cell

The formula above will give you the average of all of the values that only have a single word in the team member column if that is what you were looking for.

Lindsay AR

@L@123 Thanks. What I am looking for, is for the combined score for Scooby Doo, and a separate total score for Shaggy, and so on. I want to be able to do this without have to say =Scooby Doo or =Shaggy. I will not always know the list of names that are added.

Lindsay AR

Update: I just exported to Power BI and did all my calculations there.