how do i create a column to get the average of other columns with star ratings

looking for the average star rating with respect to the other five columns that solicit a star rating.

• ✭✭✭✭✭✭

You would need to create a new "helper" column for each of the different star columns. You would then need to use a formula to designate a numerical score based on each, average those numerical values, then convert that back into a start rating.

Does this sound like an option for you? It can also be done in a single column, but that particular formula could get a little messy unless you built out a table for the start count to numerical value conversion. Even then it could get a little lengthy with 5 different columns.

Let me know, and I will be happy to help with either solution.

• @Paul Newcome Hey Paul - I'm looking to do this within a single column (only one column in my sheet has star ratings, and I would like to add up the averages within a certain parent/child relationship), and my equation for applying numerical value isn't working - do you have any advice? Here's my formula:

=If(Stars1="Five", 5, if(stars1="Four", 4, if(stars1="Three", 3, if(Stars1="Two", 2, if(Stars1="One", 1, if(stars1="empty", 0))))))

• ✭✭✭✭✭✭

@Lauren Severe By "not working" do you mean you are getting an error, or are you getting an incorrect output?

• @Paul Newcome I'm getting an #unparseable

• ✭✭✭✭✭✭

What is the name of the column containing the stars?

• @Paul Newcome *Face Palms* It's NOT Stars, as I titled it 'Rating.' Thank you for letting me waste your time :)

• ✭✭✭✭✭✭

@Lauren Severe No worries. Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!