Formula for taking an average of duplicate rows
I'm attempting to construct a formula for taking averages of multiple ratings from several raters across three vendors.
Sheet 1 looks like:
Rater - Category - Vendor A Rating - Vendor B Rating - Vendor C Rating
Sheet 2 looks like:
Category - Vendor A Rating Avg - Vendor B Rating Avg - Vendor C Rating Avg
On sheet two, I need to get the average rating for each category and vendor. The formula should look at sheet one and determine where the category matches and then average the values in that row. I believe in Excel there is a AVGIF function to do this.
Any help is appreciated.
Comments
-
It sounds like you want to use the COLLECT function:
=AVG(COLLECT([Vendor A Rating]:[Vendor A Rating], Category:Category, “Category X”))
In this example, the function takes the average of all Vendor A ratings where the category is Category X.
More info here: https://help.smartsheet.com/function/collect
-
Hi all,
This may help in the future:
=AVERAGEIF(range, criteria, [average_range])
Returns the average of cells that meet a given criteria.Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)
Best,
Kara
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!