Complex IF AND Formulas
I'm trying to automate the RYG circles according to several columns of a Smartsheet grid for project management purposes. The logic is below (I've also attached a visualization of the logic):
IF[Proposed Product Launch Date] <TODAY,"Gray"
IF[Proposed Product Launch Date] >=TODAYAND[Deviation from Proposed Launch Date] <100,"Green"
IF[Proposed Product Launch Date] >=TODAYAND[Deviation from Proposed Launch Date]= 31 - 100AND[Completion] <75, "Yellow"
IF[Proposed Product Launch Date] >=TODAYAND[Deviation from Proposed Launch Date] >100AND[Completion] <75,"Red"
I know this will require nested IFs and nested ANDs, but I am really not advanced with Smartsheet formulas. I also don't know if I'm correctly annotating number ranges (i.e. between 31 and 100).
Can someone put me out of my misery on how to set up this complex formula?
Comments
-
Nic Larsen ✭✭✭✭✭✭Try this... I abbreviated the column names for my own sanity.
=IF([email protected]< TODAY(), "Gray", IF(AND([email protected]>= TODAY(),[email protected]< 100), "Green", IF(AND([email protected]>= TODAY(),[email protected]> 100,[email protected]< 0.75), "Red", "Yellow")
-
This worked:
=IF([Proposed Product Launch Date]1 < TODAY(), "Gray", IF(AND([Proposed Product Launch Date]1 >= TODAY(), [Deviation from Proposed Launch Date]1 < 100), "Green", IF(AND([Proposed Product Launch Date]1 >= TODAY(), [Deviation from Proposed Launch Date]1 > 31, [Deviation from Proposed Launch Date]1 < 100, Completion1 < 75), "Yellow", IF(AND([Proposed Product Launch Date]1 >= TODAY(), [Deviation from Proposed Launch Date]1 >= 100, Completion1 < 75), "Red"))))
But when I tried to correct the column name (from Proposed Product Launch Date to Anticipated Project Launch Date), I got an INVALID OPERATION. Any ideas?
-
Nic Larsen ✭✭✭✭✭✭
Try this -
=IF([Anticipated Project Launch Date]@row< TODAY(), "Gray", IF(AND([Anticipated Project Launch Date]@row>= TODAY(), [Deviation from Proposed Launch Date]@row< 100), "Green", IF(AND([Anticipated Project Launch Date]@row>= TODAY(), [Deviation from Proposed Launch Date]@row> 31, [Deviation from Proposed Launch Date]@row< 100,[email protected]< 75), "Yellow", IF(AND([Anticipated Project Launch Date]@row>= TODAY(), [Deviation from Proposed Launch Date]@row>= 100,[email protected]< 75), "Red"))))
-
Mike Wilday ✭✭✭✭✭✭One concern I see in your formulas is that percentage complete is not indicated with a decimal. Smartsheets records 75% as .75 and 50% as .5 so you will need to consider that in your formulas above. Also, be really careful where those rules are placed within the formula. Only some of your rules are based on percentages. You may want to move those up in the hierarchy so that another rule doesn't fire before it (I am not determining that they will, just that you should be sure).
I have had many occasions where a particular rule didn't fire because a rule before it was always true... the IF statement will look at each IF and determine if its true or not. When it finds one that is true, it will return it's THEN command, and stop looking at the rest of the formula. Sometimes its best to look at outliers like other statements that also require percentages sooner in the game.
-
The winner is:
=IF([Anticipated Project Launch Date]@row>=TODAY(), "Gray", IF(AND([Anticipated Project Launch Date]@row
The reason it didn't work initially is because I added the Anticipated Project Launch Date column (I was doodling around with these formulas on a separate sheet, rather than the real grid, in case I made an irreparable error), and I hadn't formatted it as a Date. It was only Text/Numbers, and so it didn't understand TODAY(). I also had the GREATER THAN and LESS THAN symbols inverted for the timing I wanted. Once I corrected those things, the formula worked. Thanks so much, everyone!
-
Mike Wilday ✭✭✭✭✭✭
I would do a functional test of your code. I don't think your final few IF statements will ever fire.
The[email protected]< 75 won't read correctly if your completion percentage is formatted using the toolbar's percentage button but perhaps you have it set up differently.
Help Article Resources
Categories
=([Contract amount]@row - [Install Labor (actual)]@row) \/ [Contract amount]@row<\/p>
Be sure the \"Percentage\" column is formatted as a percentage. Positive numbers show that your total spend is under<\/strong> the [Contract amount]. Negative values show your total spend is over<\/strong>.<\/p> You can use a similar formula to measure how far over\/under your [Labor $ (quoted)] amount is from your [Install Labor (actual)] amount.<\/p> =([Labor $ (quoted)]@row - [Install Labor (actual)]@row) \/ [Labor $ (quoted)]@row<\/p> Here, though, a negative value shows that you are OVER<\/strong> the estimate. A positive value shows you are at or UNDER<\/strong> the estimate.<\/p> Set the Sheet Summary field as text\/number then add +\"//www.santa-greenland.com/community/discussion/39596/\" to the end of the MAX function (plus quote quote) to convert it into a text string.<\/p> =MAX([Modified]:[Modified]) + \"//www.santa-greenland.com/community/discussion/39596/\"<\/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":106990,"type":"question","name":"JOIN COLLECT for Checkboxes","excerpt":"Hello All, Is there a way to collect information using a JOIN COLLECT formula? Column 2 is Name Column 3 thru 5 ASL. Columns 6 thru 8 is are the names I want to populate based on the checkboxes (ASL). Column 3 can have two checkboxes versus 1. Ultimately I would like all checkboxes that are checked to populate names in…","snippet":"Hello All, Is there a way to collect information using a JOIN COLLECT formula? Column 2 is Name Column 3 thru 5 ASL. Columns 6 thru 8 is are the names I want to populate based on…","categoryID":322,"dateInserted":"2023-06-28T02:32:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T11:45:13+00:00","insertUserID":162875,"insertUser":{"userID":162875,"name":"hello1030","url":"https:\/\/community.smartsheet.com\/profile\/hello1030","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-06-28T20:29:52+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T11:50:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3375961669,"url":"https:\/\/community.smartsheet.com\/discussion\/106990\/join-collect-for-checkboxes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106990\/join-collect-for-checkboxes","format":"Rich","lastPost":{"discussionID":106990,"commentID":383023,"name":"Re: JOIN COLLECT for Checkboxes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383023#Comment_383023","dateInserted":"2023-06-29T11:45:13+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T11:50:37+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-28T12:51:52+00:00","dateAnswered":"2023-06-28T08:50:42+00:00","acceptedAnswers":[{"commentID":382755,"body":" Hi @hello1030<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this. (I've guessed what your formula would look like, but you'd have to update the column names to match yours)<\/p> =JOIN(COLLECT([COLUMN2]:[COLUMN2], [COLUMN2]:[COLUMN2], 1, [COLUMN3]:[COLUMN3], \"Support\"), CHAR(10))<\/p> Did that work\/help? <\/p> There were a lot of errors in the formula, but it was mainly the brackets { is used for cross-sheet formulas, and you have to close the [ ] square brackets around a column name that doesn't only have one piece of text, and then the structure wasn't correct.<\/em><\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[]}">
![\"Screenshot](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/FTR07ISR2LWW\/screenshot-2023-06-28-at-8-31-35-am.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/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":107038,"type":"question","name":"Modified Date loses detail when referenced","excerpt":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected. =MAX([Modified]:[Modified]) Problem: The displayed value…","snippet":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all…","categoryID":322,"dateInserted":"2023-06-28T17:43:23+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T21:44:02+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3375964045,"url":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","format":"Rich","lastPost":{"discussionID":107038,"commentID":382970,"name":"Re: Modified Date loses detail when referenced","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382970#Comment_382970","dateInserted":"2023-06-28T21:44:02+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+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-28T21:29:15+00:00","dateAnswered":"2023-06-28T18:46:15+00:00","acceptedAnswers":[{"commentID":382932,"body":"
