# 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.

Tags:

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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.

• ✭✭✭
Options

Amazing!!! Thank you!

• ✭✭✭
Options

@jmyzk_cloudsmart_jp - Is there an add on to this formula that will leave the cell's value blank if the value = 0? I have several products that have not yet been scored, therefore the value is 0 (using IFERROR on the formula to show 0)

• ✭✭✭✭
Options

@MeganJF , add the below into the formula

IF(ISBLANK([Total Value to User Score]@row), "",

• ✭✭✭✭✭✭
Options

Hi @MeganJF