Column Formula: Work On Some Rows and Not Others

Smartsheet Community:

I have a Column Formula and it is Not Working Constantly. Take a look at the Empty Cells in Screenshot. I have also Added the Formula Below for Each Column Formula:

Average Pain Formula: =AVG([No Pain]@row:[Extreme Pain]@row)

Overall Pain Level Formula: =IF([Average Pain]@row = 0, "No Pain", IF([Average Pain]@row = 1, "Mild", IF([Average Pain]@row = 2, "Moderate", IF([Average Pain]@row = 3, "Severe", IF([Average Pain]@row = 4, "Very Severe", IF([Average Pain]@row = 5, "Extreme"))))))

Overall Emoji Pain Formula: =IF([Overall Pain Level]@row = "No Pain", "No Pain", IF([Overall Pain Level]@row = "Mild", "Mild", IF([Overall Pain Level]@row = "Moderate", "Moderate", IF([Overall Pain Level]@row = "Severe", "Severe", IF([Overall Pain Level]@row = "Very Severe", "Very Severe", IF([Overall Pain Level]@row = "Extreme", "Extreme"))))))



Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Kal

    Ah! I've just realized that the AVG is likely adding decimals to your numbers, so "5" is likely "5.5" or something similar (you can test this by adjusting the formatting on that column to show decimals from the top toolbar). Your formula is looking to see if that cell " = 5" which would only look for the round number.

    Try wrapping a ROUND Function around your Average to make sure it only outputs a round number:

    =ROUND(AVG([No Pain]@row:[Extreme Pain]@row))

    This should sort it out!



  • Kal
    Kal ✭✭
    Answer ✓

    That is the Problem, even though I have not Tried it yet. I Formatted The Cell so the Decimals would not Show. I will make the Necessary Adjustments as you Recommended and let you know if that Resolved my Issue.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!