Multiple IF Statements

07/30/18 Edited 12/09/19

Can we do multiple IF statements? I've tried to use IF and also OR to complete the function I'm trying to do and nothing seems to work. I want my Status column, "Not Started", "In Progress", and "Completed" to be based off of the % indicated in the % Completed column so I don't have to remember to change the status when the % completed changes. I know I've done this before in Excel but not sure Smartsheet can handle an IF statement with multiple conditions.

Here is the logic...

IF % Completed = 0%, then return "Not Started"

IF % Completed = 100%, then return "Completed"

IF % Completed is between 1 - 99, then return "In Progress" OR this could also be IF the first two logics are FALSE then return "In Progress".

 

Thank you,

Kim

IF Functions.PNG

Popular Tags:

Comments

  • leejoramoleejoramo ✭✭✭✭✭
    edited 07/30/18

    You chain together additional conditions within the IF function.

    =IF([% Complete]2 = 0, "Not Started", IF([% Complete]2 = 1, "Complete", "In Progress"))

    In this case, I have placed a second IF function in the False result of the first IF function. NOTE: you can put IF's or other functions in an IF functions TRUE or FALSE returns.

  • Mike WildayMike Wilday ✭✭✭✭✭

    It's important to note a couple things. Percentages, though displayed with a % sign, are actually decimals in the backend. 1 = 100%, .5 = 50%, etc. etc. 

    So if you wanted to add an additional status based on a lesser percentage, then you would use the decimal. .5, .6, etc. 

  • Thank you this was very helpful, it was the second "IF" that I was missing. :)

  • I'm getting an unparseable error and can't figure out what the issue is. Here is my formula:


    =

    IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up”,

    IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down”, 

    IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged”, “Fix Me”)))

    The test lines all worked and I placed it into a text editor and made the suggestions from the video, but it just says unparseable?

  • @Mike Wilday any idea what I could be doing incorrectly? ^

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

    Hi @Elizabeth Jones

    You have characters that are wrong. See below.

    ” “

    =

    IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up,

    IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down

    IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged, Fix Me)))


    Here's the fixed one.

    =

    IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up",

    IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down", 

    IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged", "Fix Me")))


    Did that work?

    I hope that helps!

    Be safe and have a fantastic week!

    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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • It did work! It appears the " from the text editor is causing the issue. Thanks for helping me solve this :)

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

    @Elizabeth Jones

    You're more than welcome!

    Yes, some text editors adds strange characters.

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Mike WildayMike Wilday ✭✭✭✭✭

    @Elizabeth Jones Yeah, if you use a word processor to develop your formulas, they frequently add "Smart Quotes" in place of standard quotations. It's always best practice to construct your formulas in wordpad, notepad ++ or a similar simple text-editor that strips out all formatting.

  • Can someone please tell me how to use multipe IF function using more than 1 Smartsheet? Thank you in advance.

Sign In or Register to comment.