Calculating the average of a column based on specific criteria in another group of cells

Matt Foss

Hello everyone,

I am trying to calculate the average of a column based on criteria found in another. The catch is that I don't want to specify the specific text of the other criteria as it is going to be based off of an ever expanding drop down/list of different values and I want to minimize constant manual intervention. Essentially, I want to tell the formula to look at the criteria against what's selected as the dropdown in the criteria row.

Here is what I have, the third column represents what I'm trying to gather. So I would expect every row that is Google to show an average of 3 and every row that is Apple to show an average of 4. The formula I have tried with no luck is: =AVG(COLLECT([User Rating]:[User Rating], Name:Name, Name@row))

Name User Rating Average

Google 4 ???

Apple 5

Google 3

Apple 2

Apple 5

Google 2

Matt Foss

Please disregard as this has been solved! The "User Rating" column and its associated number is driven by formula that provides a number based on a text based rating scale. The source formula had the numbers in parenthesis which made the AVG function view it as a non-number. Thanks to anyone who was trying to solve!

Matt Foss

Better chart for reference.

Matt Foss

Please disregard as this has been solved! The "User Rating" column and its associated number is driven by formula that provides a number based on a text based rating scale. The source formula had the numbers in parenthesis which made the AVG function view it as a non-number. Thanks to anyone who was trying to solve!

Genevieve P.

Hi Matt,

I'm glad to hear you sorted it out! That's a tricky solve, well-done! Thanks also for sharing your solution.