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

Options
✭✭✭✭

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

Apple 5

Apple 2

Apple 5

Tags:

• ✭✭✭✭
Options

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!

• ✭✭✭✭
Options

Better chart for reference.

• ✭✭✭✭
Options

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!

• Employee
Options

Hi Matt,

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