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
Check out the Formula Handbook template!