Calculating the average of a column based on specific criteria in another group of cells
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
Best Answer
-
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!
Answers
-
Better chart for reference.
-
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!
-
Hi Matt,
I'm glad to hear you sorted it out! That's a tricky solve, well-done! Thanks also for sharing your solution.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!