Multiple IF Statements

kadam56146
edited 12/09/19 in Formulas and Functions

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

Tags:

Best Answer

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭
    edited 07/30/18 Answer ✓

    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.

«13

Answers

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭
    edited 07/30/18 Answer ✓

    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 Wilday
    Mike 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 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.

  • 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 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.

  • Mike Wilday
    Mike 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.

  • I am trying to use multiple IF statements and it is not working. For example, I have a column called "HCSA Goal Alignment" which has three conditions that a user can choose. The three conditions are:

    ·       Does not meet (Low priority = 5 points)

    ·       Partially meets (Medium priority = 10 points)

    ·       Completely meets (High priority = 15 points)

     

    I have created another column called "Score" where I am trying to create a formula with multiple IF statements to calculate the result (5, 10, or 15) based on the above three inputs. Here is my formula:

     

    ·       =IF([HCSA Goal Alignment]@row = "Does not meet (Low priority =5 points)", "5", IF([HCSA Goal Alignment]@row = "Partially meets (Medium priority =10 points)", "10", IF([HCSA Goal Alignment]@row = "Completely meets = (High priority = 15points)", "15")))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Sharad Jain

    The formula is structured correctly! However your quotes around the desired output (ex. "5") turn the numbers into text values.

    Try tis:

    =IF([HCSA Goal Alignment]@row = "Does not meet (Low priority =5 points)", 5, IF([HCSA Goal Alignment]@row = "Partially meets (Medium priority =10 points)", 10, IF([HCSA Goal Alignment]@row = "Completely meets = (High priority = 15points)", 15)))


    Keep in mind the values it's looking for in your "HCSA Goal Alignment" column will need to be spelled exactly the same in your formula.

    If this doesn't work, can you post a screen capture of the values in the drop-down list, and the error or incorrect result you're seeing?

    Cheers,

    Genevieve

  • Hi, I am trying to nest two IF statements that reference other sheets. Each statement alone works:


    =IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0)))

    =IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))


    But if I try to nest them like this I get an incorrect argument set error. What am I doing wrong?

    =IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0, =IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))))

  • Removed the extra = and I get the same error


    =IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0, IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @william.johnson

    It looks like a few parentheses were out of place.

    =IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0)), IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0))))

    Does this work for you?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!