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
-
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,
应该是
- 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
-
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,
应该是
- 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.
-
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)
-
jmyzk_cloudsmart_jp ✭✭✭✭✭
帮助文章资源欧宝体育app官方888
Categories
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T02:58:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-30T22:42:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-30T22:42:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"