What's wrong with this formula?

It returns only Complete or Not Started.

=IF([% Complete]@row = "1", "Complete", IF([% Complete]@row = "75", "End in Sight", IF([% Complete]@row = "50", "Work in Progress", IF([% Complete]@row = "25", "Just Started", "Not Started"))))

Tags:

Best Answers

  • Ken Hankoff
    Ken Hankoff ✭✭✭✭
    Answer ✓

    Thanks, Paul. Close, but I'm still missing something.

    What I'm trying to do is build a formula with logic that evaluates and returns as follows, and I think I've perhaps got things in the wrong order:

    If % Complete is 0 display "Not Started"

    If % Complete is greater than zero and up to 25% display "Just Started"

    If % Complete is greater than 25% and less than 75% display "Work in Progress"

    If % Complete is greater than 75% and less than 100% display "End in Sight"

    If % Complete is 100% display Complete

    Again - I think I'm close with the formula I built and your AND suggestion, but still not working.

    Thoughts?

    Here it is again: =IF([% Complete]@row = "1", "Complete", IF(AND([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(AND([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(AND([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Ken Hankoff

    Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1.

    You'd need to use decimal values instead for it to work.

    25% = 0,25 (0.25)

    50% = 0,5 (0.5)

    100% = 1 

    Depending on your country/region, you'll need to exchange the comma to a period.


    Did that work?

    I hope that helps!

    Be safe and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Ken Hankoff
    Ken Hankoff ✭✭✭✭

    Hi Andree,

    That worked (after I made the changes you suggested, and figured out that .050 wouldn't work :-).

    I've also got a bigger challenge for you. I'm wondering if there is a way to create a formula that takes this a step further, so that it works on the Parent rows in the hierarchy.

    In other words, when the Parent row in the % Complete column automatically (because dependencies are enabled) calculates a value (e.g. 28%), can rules for the value in the Status column (where this formula sits) be made to return a similar result? For example, anything greater than 0% and less than 25.01% would be "Just Started". Anything between 25.01% and 50.01% would be "Work in Progress", etc.

    Can it be done?

  • Ken Hankoff
    Ken Hankoff ✭✭✭✭
    edited 08/26/20

    In the event you think I'm just mooching off of your brilliance (Andree), I did want you to know I am attempting to solve this one on my own.

    Although it is not (yet) functioning as expected, I do feel I am getting close with this formula:

    =IF([% Complete]@row = "1", "Complete", IF(OR([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(OR([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(OR([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try replacing your OR functions with AND functions.

  • Ken Hankoff
    Ken Hankoff ✭✭✭✭
    Answer ✓

    Thanks, Paul. Close, but I'm still missing something.

    What I'm trying to do is build a formula with logic that evaluates and returns as follows, and I think I've perhaps got things in the wrong order:

    If % Complete is 0 display "Not Started"

    If % Complete is greater than zero and up to 25% display "Just Started"

    If % Complete is greater than 25% and less than 75% display "Work in Progress"

    If % Complete is greater than 75% and less than 100% display "End in Sight"

    If % Complete is 100% display Complete

    Again - I think I'm close with the formula I built and your AND suggestion, but still not working.

    Thoughts?

    Here it is again: =IF([% Complete]@row = "1", "Complete", IF(AND([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(AND([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(AND([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))

  • Ken Hankoff
    Ken Hankoff ✭✭✭✭

    That's great, Paul. Your suggested formula adjustment worked.

    I was wondering whether my use of OR and AND was somehow overkill. Clearly you've shown that it was. I need to work on my understanding of the flow of the logic, as this should not be so difficult (for me).

    Thank you!!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Ken Hankoff

    Haha!

    Glad you got it working!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!