Formula to Assign Symbol based on a cell's value
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!
Best Answer
-
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
-
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.
-
Amazing!!! Thank you!
-
@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)
-
@MeganJF , add the below into the formula
IF(ISBLANK([Total Value to User Score]@row), "",
-
-
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!
-
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:
- Select the Column: Click on the column header where you want the arrows to appear.
- Open Column Properties: Click the dropdown arrow next to the column name and select "Edit Column Properties."
- Change Column Type: Set the column type to "Symbol."
- 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.
- 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.
-
@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 -
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!