Formula to Assign Symbol based on a cell's value

Options

This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis.

What I am trying to do: (Using the pain scale symbols)

Have symbols automatically changed based on the value in the corresponding cell.

I am already using a formula to assign a value based on the emoji so that I can get the average scores. Therefore I tried to reverse this by trying the following formula -

=IF([Total Value to User Score]@row = <4.8, "No Pain", IF([Total Value to User Score]@row = <3.8, >4.7, "Mild", IF([Total Value to User Score]@row = <2.8, >3.7, "Moderate", IF([Total Value to User Score]@row = <1.8, >2.7, "Very Severe", IF([Total Value to User Score]@row = <0, >1.7, "Extreme")))))

For reference, the round up values scores are:

4.8 - 5 = No Pain

3.8 - 4.7 = Mild

2.8 - 3.7 = Moderate

1.8 - 2.7 = Very Severe

0 - 1.7 = Extreme

At the moment I am having to manually update these which, as you can guess, is leading to errors, because data shifts and I don't catch it until I am presenting it to my leadership team.

NOTE: I have also tried automation, but the column does not come up as an option to change a cell value in.

Thank you in advance for your help!

Tags:

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @MeganJF

    I guess you forget to use AND( ).

    For example, 

    • IF([Total Value to User Score]@row = <3.8, >4.7, "Mild,

    should be

    • IF(AND([Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7), "Mild",

    However, the following would be more straightforward.

    • =IF([Total Value to User Score]@row >= 4.8, "No Pain", 
    • IF([Total Value to User Score]@row >= 3.8, "Mild", 
    • IF([Total Value to User Score]@row >= 2.8, "Moderate", 
    • IF([Total Value to User Score]@row >= 1.8, "Very Severe", 
    • IF([Total Value to User Score]@row < 1.8, "Extreme", "")))))


    Please take a look at the published sheet, as well.


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!