Help with IF formula

Hey Smartsheet Community,

 I am really not that great with making long formulas, especially with the "IF" formula. Could someone please help me with making a formula for the RYG balls where if the task is less than 50% complete turn red, if the task is between 50%-99% complete, turn yellow and if the task is 100% complete turn green. I appreciate any help you could give me on this.

«1

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    This formula should do the trick for you. Replace % column title with the actual title of your column. It's set up for row 23... so adjust the row number from 23 to whichever row you want to reference. 

    =IF([% column title]23 < .5 ,"Red", IF([% column title]23 < .99, "Yellow", IF([% column title]23 = 1, "Green")))

     

  • Very helpful Mike, thank you!!!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Glad I could be of service! smiley

  • Hello,

     

    Can anyone help me with this formula from Excel I need to transpose into Smartsheet?

    =IF(A1="ok";(B1/1,2*0,2);0)

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Sure! 

    In Excel A and B refer to columns. Smartsheets refers to columns by the column names. If your column names contains spaces or ends in a number, then you would wrap your column name in brackets. 

    =IF([Name of Column A]1="ok", [Name of column b]1/1... 

    But the math and construct of that IF statement doesn't make a lot of sense to me. 

    IF

        A1 = "ok", 

    THEN

       B1 divided by 1

    ELSE

       2 times 0

    ----- this is where the formula starts to confuse me ----

    ELSE

        2

    ELSE

        0

  • I have (after much trial and error and with help from several coworkers) finally gotten a formula to work which returns the following health statuses:

    • yellow if the status is In Progress and the end date is in the past
    • red if the status is Not Started and the end date in the past
    • green if the status is Complete (no date reference)

    It works either of the following ways: 

    =IF(AND(Status272 = "In Progress", TODAY() > [End Date]272), "Yellow", IF(Status272 = "Complete", "Green", IF(AND(Status272 = "Not Started", TODAY() > [End Date]272), "Red")))

    =IF(AND(Status272 = "In Progress", TODAY() > [End Date]272), "Yellow", IF(AND(Status272 = "Not Started", TODAY() > [End Date]272), "Red", IF(Status272 = "Complete", "Green")))

    I want to add another condition, which is:

    • yellow if the status is Not Started and the start date is in the past. 

    This is how it would be expressed in the formula:

    IF(AND(Status272 = "Not Started", TODAY() > [Start Date]272), "Yellow",

    I've tried a bunch of different ways to add this string, to no avail. I believe I need a nested OR statement in order to do this, but I can't figure out the correct syntax.  

    Can anyone help?   THANK YOU!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try this one. I would also recommend replacing the row number with @row to make this formula more copy and pasteable. 

    =IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red"))))

  • jdwood1105931
    edited 10/04/19

    For some reason the red portion of this won't work in the string.  If I take it out and use it by itself it works.  I'm going crazy trying to puzzle this out. I reversed the order of the red and green statements and that didn't make any difference.  HELP!  See screenshot of results compared to expected results.  Everything works, except the red...

     

    Screenshot using the above formula.PNG

  • I figured out the @row trick yesterday by reading a bunch of other posts.  That is great!

  • I don't understand why this works, but I switched the order so the yellows are last and now it works!

    = IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow"))))

    Screenshot using the NEW formula.PNG

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. Glad you figured it out! 

  • Now I want to add the gray ball and use it if the start date field is blank.  I tried the formula below and it doesn't work.  Any ideas? 

    =IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow", IF(ISBLANK([Start Date]@row), "Gray")))))

     

    After I started to add this I also worried that some Completed tasks might have had the dates deleted and I don't want those to turn Gray, so I might need to add another caveat to the green portion of the formula.  This is getting awfully complicated. 

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    What are you getting instead, for rows that should be grey? Have you tried moving the gray IF to the beginning of the function? 

  • It was red instead, but now it is gray using this sequence in the formula.  But I have confirmed my fears that if a status is Complete and the date is blank it is gray, when I want green.  Can I add a bit that means if status is not Complete to the gray portion? I'm sorry I don't have a good enough understanding of all the possible syntax issues to solve this on my own...

    =IF(ISBLANK([Start Date]@row), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow")))))

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

    Hi,

    Try this.

    =IF(AND(ISBLANK([Start Date]@row), Status@row <> "Complete"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow")))))

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    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!