How to get the average of symbol type (stars) column?

shaynelois

Hi! I'm a virtual assistant new to Smartsheet and I was tasked to replicate a skills assessment worksheet template. It uses the symbol (stars) ratings so I can get the average score and the number of skills that were rated using the stars.

For the average stars, I tried to use the same formulas in the template but Lookup1 wasn't working so I referenced the column name itself, but it doesn't calculate correctly.

Average Stars

OG Formula: ="A1 Average Stars" + " = " + ROUND(AVG(CHILDREN([Lookup1]8), 2))

My Formula: ="A1 Average Stars" + " = " + ROUND(AVG((Stars8:Stars18), 2))

For the skills assessment, it doesn't count anything even though I tested it by putting star ratings on the skills (once I put a star, it should show 9 out of 10 skills assessed).

Skills Assessment:

OG Formula: =COUNTIF((CHILDREN([Lookup1]8)), >0) + " of " + (COUNT(CHILDREN(Info8))) + " Skills Assessed"

My Formula: =COUNTIF(Stars9:Stars18, >0) + " of " + COUNT(Stars9:Stars18) + " Skills Assessed"

The pictures attached show my formulas since I can't show the template for data purposes

DKazatsky2

Hi @shaynelois,

The reason your formulas are not working is because the "Stars" column is not equating to a number, but rather a word, which you cannot do math against. When you have 5 stars selected, the value is actually "Five".

I suggest you create a "helper" column, which can be hidden if needed, with the following.

=IF(Stars@row = "Five", 5, IF(Stars@row = "Four", 4, IF(Stars@row = "Three", 3, IF(Stars@row = "Two", 2, IF(Stars@row = "One", 1, IF(Stars@row = "empty", 0))))))

Now, all your formulas can use this new column for calculation.

Hope this helps,

Dave