Updating a status column based on Values in Expected % Complete and % Complete

Options

I have dates from two columns, Expected % Complete, and % Complete which I want to use, and populate the Status Column (Dropdown) using Formulas:

The Dropdown options are " Not Started", "Behind", "On Track", and "Complete". I tried using the below formula


=IF(AND([% Complete]@row = "100%", ([Expected % Complete]@row < "100%")),"Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On

Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")),

"Behind", "Not Started")))

How to fix this ?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi Mohammed,

    The structure of this is great, well-done! However, when referencing a number column that has the % symbol turned on, you want to reference percentages as decimals.

    For example, instead of saying "100%", you would write the number 1.


    Try this:

    =IF(AND([% Complete]@row = 1, ([Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > 0)), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > 0)), "Behind", "Not Started")))


    You can read more about this on our Formula FAQ page (click here).

    Cheers!

    Genevieve

  • Mohammed Sulaiman
    Mohammed Sulaiman ✭✭✭✭
    edited 01/27/20
    Options

    Hi Genevieve,

    I did try the above solution, and it worked. Thanks for your help.

    However, I added another condition, where the status becomes "Not Started/Behind".


    =IF([% Complete]@row = 1, ([Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > 0)), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > 0)), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))).

    This additional condition " IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row = 0)), "Not Started/Behind" " is to show items, that are supposed to start but are behind, as the Expected % Complete > 0, and % Complete = 0.

    However, when I add this formula, the "Not Started" status doesn't populate as it should, when the Expected % Complete = 0, % Complete = 0. Instead the "Not Started/Behind" populates in the cell.


    How to fix this ?


    Thanks,

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi Mohammed,

    This formula has a number of extra closing parentheses that should not be there. When you use "AND" you don't need to add more parenthesis to close of different instructions. Additionally, when you "nest" IF statements together in a row, leave the last parenthesis to be closed off at the very end of the entire formula.

    Try this instead:

    =IF(AND([% Complete]@row = 1, [Expected % Complete]@row < 1), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, [% Complete]@row > 0), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row > 0), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row = 0), "Not Started/Behind", "Not Started"))))

  • AmyD
    AmyD ✭✭
    Options

    @Genevieve P. I tried this formula, and When I have a task that is Completed, showing 100% in the % complete, and the expected completion is also 100%, it returns "On Track" in the status column, vs "Complete", any suggestions for correcting this?


  • Genevieve P.
    Genevieve P. Employee Admin
    edited 10/28/21
    Options

    Hi @Amy D

    Great catch! You'll want to add in a statement that says if they're both 1, return "Complete".

    =IF(OR(AND([% Complete]@row = 1, [Expected % Complete]@row = 1), AND([% Complete]@row = 1, [Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, [% Complete]@row > 0), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row > 0), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row = 0), "Not Started/Behind", "Not Started"))))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!