Combining IF and IF AND Formulas

Options
Jodi C
Jodi C ✭✭✭
edited 11/10/22 in Formulas and Functions

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?

Tags:

Best Answer

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!