Trouble building formula with "number of stars"

Options

Hi, I am building a training feedback analysis for my team, but having difficult with the formula to count the "star".

Looking the columns: I have no difficulties to turn the value 4 (in content satisfaction column) into 4 stars (star column).

However in the Sheet Summary, the first field is an average from the Content Satisfaction column.

In the Content Satisfaction Star, I wanted to convert the first field into star.

I use this formula, but it's #UNPARSEABLE:

=IF([How satisfied were you with the training content?]# = "1", "One", IF([How satisfied were you with the training content?]# = "2", "Two", IF([How satisfied were you with the training content?]# = "3", "Three", IF([How satisfied were you with the training content?]# = "4", "Four", IF([How satisfied were you with the training content?]# = "5", "Five"‌‌‌‌)))))‌‌


Can you advise what went wrong?

Even when I try to "=AVG(Star:Star), there is still error.


How can I have the average to be viewed in the star symbol?


Your assistance and guide would be much apprec

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Vivien Chong

    I hope you're well and safe!

    Try something like this.

    =IF([How satisfied were you with the training content?]# = 1, "One", IF([How satisfied were you with the training content?]# = 2, "Two", IF([How satisfied were you with the training content?]# = 3, "Three", IF([How satisfied were you with the training content?]# = 4, "Four", IF([How satisfied were you with the training content?]# = 5, "Five")))))

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    Hi @Andrée Starå , I am doing fine here. Thanks.

    I edited the formula, it is still #UNPARSEABLE.

    This is a Symbol field with the "5 stars".

    I wondered what could have gone wrong here.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Vivien Chong

    I'm surprised to see that error since your formula lights up correctly. I also tested it on my own sheet and the logic is set up correctly.

    Your field could just be displaying the error from a caching issue from the previous formula. Could you try logging out/in again to see if this refreshes your sheet? Or try adding a new Summary Field as a Symbol field and add in the formula to a fresh field (deleting the old one).

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    Hi @Genevieve P and @Andrée Starå thanks for your assistance.

    I finally figured out why the formula didn't work in my case.

    Because my [How satisfied were you with the training content?]# is a formula to calculate the average from the sheet. And the average sum may have some decimal points in it. But I use the decrease decimal to remove the decimal points.

    I have changed the formula to less than (<), rather than equals to (=) and it works. Thanks.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    I'm glad you were able to figure it out! Thanks for sharing your solution.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!