Combining IF and IF AND Formulas
I have three formulas below that work as individual formulas. I am trying to combine them in my Status column into one formula so my cell automatically updates based on the percent shown in the % complete cell. I thought it should be =IF([% Complete]@row = 0, "Not Started"), IF(AND([% Complete]@row > 0.01, [% Complete]@row < 1), "In Progress"), IF([% Complete]@row = 1, "Complete") but that does not seem to work.
Basically, if the % complete is 0 then it should return "Not started", if it's between 1%-99/100% then it should return "In Progress" and if it's 100% it should return "Complete".
IF([% Complete]@row = 0, "Not Started")
IF(AND([% Complete]@row > 0.01, [% Complete]@row < 1), "In Progress")
IF([% Complete]@row = 1, "Complete")
I am confused because each formula works on its own but they do not work together. What am I doing wrong?
Best Answer
-
Hi @Jodi C,
I automated my Status column the same way using this.
=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row >= 0.01, [% Complete]@row < 1), "In Progress", "Complete"))
Hope this helps!
BRgds,
-Ray
Answers
-
Hi @Jodi C,
I automated my Status column the same way using this.
=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row >= 0.01, [% Complete]@row < 1), "In Progress", "Complete"))
Hope this helps!
BRgds,
-Ray
-
@Ray Lindstrom Thank you!
-
Quick note about nested IFs...
For the formula to move to the second IF, the first must be false. For it to move to the third, the first and second must be false, so on and so forth.
Leveraging that (and assuming you won't have a negative %), you can actually simplify it a little bit by getting rid of the AND function along with the greater than or equal to .01 portion.
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))
It doesn't make a huge difference in this specific case, but it is something to keep in mind when you start stacking more and more IFs on.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …