Simple IF function not working

Myra Jane Lim
Myra Jane Lim ✭
inFormulas and Functions

Hello,

I have an issue with a simple IF formula. Here's the formula: =IF(Completion1 = "100%", "Closed", "Open") for Overall Progress Column.

It's not working. Even if the value is NOT equals to "100%", the value showing is the false one (Open). I don't know why it's not working.

A note: The logical expression (Completion column) I am evaluating counts the average of checkboxes marked across a row. Does this mean the IF column does not work in marked checkboxes?

Please let me know if you have alternatives or suggestions. Any ideas are much appreciated. Thanks so much!

· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @KarenTF<\/a>,<\/p>

You can do this with a nested IF statement. As a basic example, if you had a dropdown with options A, B, C and wanted to assign them points values of 1, 2, 3 respectively, you would use the following formula in your points column:<\/p>

=IF([Radio selection]@row = \"A\", 1, IF([Radio selection]@row = \"B\", 2, IF([Radio selection]@row = \"C\", 3)))<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

If you have multiple instances of this then you could then use a SUM formula to get the total at the end.<\/p>

Hopefully this points you in the right direction, but if you've more specifics or have any problems\/questions then just post!<\/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":111115,"type":"question","name":"Can Smartsheet automatically push a date on to a Thursday?","excerpt":"Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code\/formula for Smartsheet to check whether an End Date (publication date) falls on a Thursday and for Smartsheet to automatically move the date along to the…","snippet":"Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code\/formula for…","categoryID":322,"dateInserted":"2023-10-03T14:07:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-03T15:17:08+00:00","insertUserID":167869,"insertUser":{"userID":167869,"name":"Lee5","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Lee5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-03T14:58:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-03T15:11:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":25,"score":null,"hot":3392690701,"url":"https:\/\/community.smartsheet.com\/discussion\/111115\/can-smartsheet-automatically-push-a-date-on-to-a-thursday","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111115\/can-smartsheet-automatically-push-a-date-on-to-a-thursday","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":111115,"commentID":398125,"name":"Re: Can Smartsheet automatically push a date on to a Thursday?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398125#Comment_398125","dateInserted":"2023-10-03T15:17:08+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-03T15:11:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-03T14:58:34+00:00","dateAnswered":"2023-10-03T14:21:50+00:00","acceptedAnswers":[{"commentID":398101,"body":"

@Lee5<\/a> So I can make it so it falls on the current week Thursday, so if it is a Friday it will slide back 1 day.<\/p>

The formula is =[End Date]@row - WEEKDAY([End Date]@row) + 5<\/p>"},{"commentID":398102,"body":"

Give this a try:<\/p>

=[Publication Date]@row + (5 - WEEKDAY([Publication Date]@row)) + IF(WEEKDAY([Publication Date]@row) > 5, 7, 0)<\/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"},{"tagID":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":111101,"type":"question","name":"I want the color to change to green if checkbox is checked and stay Red if it is not.","excerpt":"I have this formula but it is not working for me. =IF(Done@row = 1, Status@row = \"Green\", Status@row = \"Red\") I have 2 columns, one is a checkbox column, and one is a symbol column with Green, Yellow and Red options. I want the color to change to green if checkbox is checked and stay Red if it is not. Can someone pls help…","snippet":"I have this formula but it is not working for me. =IF(Done@row = 1, Status@row = \"Green\", Status@row = \"Red\") I have 2 columns, one is a checkbox column, and one is a symbol…","categoryID":322,"dateInserted":"2023-10-03T11:38:22+00:00","dateUpdated":null,"dateLastComment":"2023-10-03T12:43:00+00:00","insertUserID":167861,"insertUser":{"userID":167861,"name":"jurgen93","url":"https:\/\/community.smartsheet.com\/profile\/jurgen93","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!x0dqcfebITo!2TEp_ivMMPo!Pdo2cLhE7c-","dateLastActive":"2023-10-03T18:08:09+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167861,"lastUser":{"userID":167861,"name":"jurgen93","url":"https:\/\/community.smartsheet.com\/profile\/jurgen93","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!x0dqcfebITo!2TEp_ivMMPo!Pdo2cLhE7c-","dateLastActive":"2023-10-03T18:08:09+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3392671282,"url":"https:\/\/community.smartsheet.com\/discussion\/111101\/i-want-the-color-to-change-to-green-if-checkbox-is-checked-and-stay-red-if-it-is-not","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111101\/i-want-the-color-to-change-to-green-if-checkbox-is-checked-and-stay-red-if-it-is-not","format":"Rich","lastPost":{"discussionID":111101,"commentID":398049,"name":"Re: I want the color to change to green if checkbox is checked and stay Red if it is not.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398049#Comment_398049","dateInserted":"2023-10-03T12:43:00+00:00","insertUserID":167861,"insertUser":{"userID":167861,"name":"jurgen93","url":"https:\/\/community.smartsheet.com\/profile\/jurgen93","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!x0dqcfebITo!2TEp_ivMMPo!Pdo2cLhE7c-","dateLastActive":"2023-10-03T18:08:09+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-03T12:43:06+00:00","dateAnswered":"2023-10-03T11:54:39+00:00","acceptedAnswers":[{"commentID":398043,"body":"

Hey @jurgen93<\/a> <\/p>

I think the formula you are looking for is this one :=IF(<\/strong>Done@row = \"true\", \"Green\", \"Red\")<\/strong><\/p>

It worked for me but let me know if I missed anything.<\/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

  • I want to assign a specific value for when a dropdown selection was made for a column.

    If I wanted to assign a specific value for when a dropdown selection was made for a column, what formula should I use. I'm trying to assign a point-value in a separate column that is dependent on the selection made from a dropdown item in another column. For instance, say in a form the user selects a fixed radio button,…

  • Can Smartsheet automatically push a date on to a Thursday?

    Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code/formula for Smartsheet to check whether an End Date (publication date) falls on a Thursday and for Smartsheet to automatically move the date along to the…

  • I want the color to change to green if checkbox is checked and stay Red if it is not.

    I have this formula but it is not working for me. =IF(Done@row = 1, Status@row = "Green", Status@row = "Red") I have 2 columns, one is a checkbox column, and one is a symbol column with Green, Yellow and Red options. I want the color to change to green if checkbox is checked and stay Red if it is not. Can someone pls help…