Formula to Assign Symbol based on a cell's value

MeganJF

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!

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=ef585943fa254224991d3e1a0762da5b

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=ef585943fa254224991d3e1a0762da5b

MeganJF

Amazing!!! Thank you!

MeganJF

@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)

jpaul

@MeganJF , add the below into the formula

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

jmyzk_cloudsmart_jp

Hi @MeganJF

Please add

 =IF([Total Value to User Score]@row = 0, ""

in front of the formulas.

KBedal

I am trying to do something very similar, but utilizing some directional arrows. I was able to input the formula but I do not know how to get the symbols to display. For example I typed down arrow, but the formula is actually displaying the words down arrow instead of a symbol. How can I get the symbols themselves to appear using the formula? Thanks!

jmyzk_cloudsmart_jp

Hi @KBedal

To display directional arrows as symbols in Smartsheet, you need to set the column's property to use symbols. Here's how you can do it:

1. Select the Column: Click on the column header where you want the arrows to appear.
2. Open Column Properties: Click the dropdown arrow next to the column name and select "Edit Column Properties."
3. Change Column Type: Set the column type to "Symbol."
4. Choose a Symbol Set: From the symbol set options, choose the set that includes directional arrows. Smartsheet provides various options, such as up, down, left, and right arrows.
5. Apply the Changes: Click "OK" to save your changes.

When you input or use a formula that refers to symbols, the directional arrow symbols should display correctly instead of the words.

https://app.smartsheet.com/b/publish?EQBCT=53b8cc06232d4a7b8e581e16290ce2e0

KBedal

@jmyzk_cloudsmart_jp thank you. I had the column set to the symbols already, but I am struggling with how to phrase the key words in the formula to generate the symbol when run. I've tried a few variations like "down", "down arrow", "angled arrow", etc. pulled from the symbol descriptions in the column type but end up with the words instead of a symbol as displayed below. Any suggestions on the key word to input so the formula runs as intended?

=IF(CONTAINS(0, [Task level]@row), [Task Name]@row, IF(CONTAINS(1, [Task level]@row), "Down arrow" + [Task Name]@row, IF(CONTAINS("2+", [Task level]@row), "Angled" + [Task Name]@row, IF(CONTAINS(2, [Task level]@row), " - - " + [Task Name]@row, IF(CONTAINS(3, [Task level]@row), " - - - " + [Task Name]@row)))))

Down arrowPlanning
AngledChange Control

jmyzk_cloudsmart_jp

Hi @KBedal

You need to precisely put the symbol word or value as specified by the Symbols column type.
To check the symbol value, you can add a helper column, like "Symbols Text" and put a formula like this;

=Symbols@row

The following two images show that the symbol values change depending on the language settings.

A sample formula to change the Symbols by Task Level is as follows:

=IF([Task Level]@row = 0, "Down", IF([Task Level]@row = 1, "Angle Down", IF([Task Level]@row = 2, "Sideways", IF([Task Level]@row = "2+", "Angle Up", IF([Task Level]@row = 3, "Up")))))

In your formula, " "Down arrow" + [Task Name]@row", for example, needs to be changed to just "Down", precisely the same symbol value with no Task Name added.

https://app.smartsheet.com/b/publish?EQBCT=636f2c7c4d804bea8ea39dbb865d6568