How to compute Average Ratings on a Subset of Rows

Robbie Rivera

Trying to put a formula that will compute the average rating based on organizational grouping.

I have a smartsheet that has a drop down field which includes organization functions like: engineering, IT, Sales, Marketing, ...

I have another field that is a numeric rating for these organizational functions.  1 thru 5. 

I need a report that will aggregate and display by organization and their corresponding average ratings.  Something like

Organization                    Average Rating

Engineering                     5

Sales                               4.5

IT                                     3

...

I know how to use Sights dashboard to display the results, but need help in putting the formula(s) together.  Thanks in advance for any help

Marcus Odum

Easiest way is to use the collect function. See link below for details.

 

Example

=AVG(Collect(Rating:Rating, Organization:Organization, "Engineering"))

 

https://help.smartsheet.com/function/collect

 

Robbie Rivera

Thank you, Marcus!  This is exactly what I was looking for - it works great.