Calculating the average of a column based on specific criteria in another group of cells
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 ???
Help Article Resources
Check out the Formula Handbook template!