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!

Tags:

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭
    Answer ✓

    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", "")))))
    Formula to Assign Symbol based on a cell's value.png


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


Answers

帮助文章资源欧宝体育app官方888

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Awesome! I'm glad it's working for you. 👍️<\/span><\/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":[]},{"discussionID":107143,"type":"question","name":"How can I get (on a separate metric sheet) the average SLA time within a given month?","excerpt":"I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement SLA column and a \"date of request\" (auto column for when the submission was created), and I'm looking to record the average for each month on a…","snippet":"I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement…","categoryID":322,"dateInserted":"2023-06-30T15:31:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T17:30:08+00:00","insertUserID":159225,"insertUser":{"userID":159225,"name":"laney_white","url":"https:\/\/community.smartsheet.com\/profile\/laney_white","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5FvXObtmaHw!CVFXQvc68Xo!anynrNoWe-v","dateLastActive":"2023-06-30T17:19:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-01T05:33:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3376287114,"url":"https:\/\/community.smartsheet.com\/discussion\/107143\/how-can-i-get-on-a-separate-metric-sheet-the-average-sla-time-within-a-given-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107143\/how-can-i-get-on-a-separate-metric-sheet-the-average-sla-time-within-a-given-month","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107143,"commentID":383369,"name":"Re: How can I get (on a separate metric sheet) the average SLA time within a given month?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383369#Comment_383369","dateInserted":"2023-06-30T17:30:08+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-01T05:33:16+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ZXS917R2FJKC\/master-sheet-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"master sheet.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T17:19:35+00:00","dateAnswered":"2023-06-30T15:50:27+00:00","acceptedAnswers":[{"commentID":383345,"body":"

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":"

@jcabaniss<\/a> , swap out this:<\/p>

=[Target End Date]1<\/p>

with this:<\/p>

=Index([Target End Date]:[Target End Date], 1)<\/p>

the second value in an index function is the row—usually we make this a MATCH lookup function but it can also be a number.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions