Combining IF and IF AND Formulas

Jodi C

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?

Ray Lindstrom

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

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

Jodi C

@Ray Lindstrom Thank you!

Paul Newcome

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.