AVG error message (with formula issues as well)

Options

I'm trying to calculate the average score (1 to 5) of a column, but keep getting the DIVIDE BY ZERO message.

The column with the scores is calculated via a formula from a column formatted with stars (from a survey form). So four stars from the survey = 4. But I noticed that all the results have a ' in front of them (e.g. '4, '5). So I'm assuming this is why I'm getting the DIVIDE BY ZERO message - they aren't being recognized by numbers.

Why is this happening? How do I get just the number without the '?

FYI here's the formula: =IF([Overall satisfaction with the session]@row = "⭐️", "1", IF([Overall satisfaction with the session]@row = "⭐️⭐️", "2", IF([Overall satisfaction with the session]@row = "⭐️⭐️⭐️", "3", IF([Overall satisfaction with the session]@row = "⭐️⭐️⭐️⭐️", "4", IF([Overall satisfaction with the session]@row = "⭐️⭐️⭐️⭐️⭐️", "5")))))

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓
    Options

    @amy_ilearning

    Remove the "" from around your numbers. It's not needed. What that is doing is making it a text value. Instead you want.

    =IF([Overall satisfaction with the session]@row = "One", 1, IF([Overall satisfaction with the session]@row = "Two", 2, IF([Overall satisfaction with the session]@row = "Three", 3, IF([Overall satisfaction with the session]@row = "Four", 4, IF([Overall satisfaction with the session]@row = "Five", 5)))))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓
    Options

    @amy_ilearning

    Remove the "" from around your numbers. It's not needed. What that is doing is making it a text value. Instead you want.

    =IF([Overall satisfaction with the session]@row = "One", 1, IF([Overall satisfaction with the session]@row = "Two", 2, IF([Overall satisfaction with the session]@row = "Three", 3, IF([Overall satisfaction with the session]@row = "Four", 4, IF([Overall satisfaction with the session]@row = "Five", 5)))))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • amy_ilearning
    Options

    That's it! Thank you so much!!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!