Multiple IF Statements

2

Answers

  • IPC
    IPC ✭✭✭

    Hi,

    I'm trying to do a multiple IF formula but just can't get it to work. Any help is highly appreciated.

    =IF((Status@row = " Not Started", "0%"),

    IF(Status@row = " Initial Progress", "25%"),

    IF(Status@row = " Mid-way Progress", "50%"),

    IF(Status@row = " Major Progress", "75%"),

    IF(Status@row = " Under approval", "90%"),100%)

    @Andrée Starå

    Thanks,

    Irene

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @IPC

    Your logic is correct however typically the Status column is looking for percentages written as decimals. Note that numbers generally are not enclosed in quotes as smartsheet might confuse them as Text.

    When nesting IF statements, note how the IF statement isn't closed off until the very end. As a rule of thumb you will have as many closing parentheses as you do the number of 'IFs'.

    =IF((Status@row = " Not Started", 0, IF(Status@row = " Initial Progress", 0.25, IF(Status@row = " Mid-way Progress", 0.5, IF(Status@row = " Major Progress", 0.75, IF(Status@row = " Under approval", 0.9, 1))))

    Does this work for you?

    Kelly

  • IPC
    IPC ✭✭✭

    Hey Kelly,

    Thank you very much! yes it worked.

    Most important part of the learning " As a rule of thumb you will have as many closing parentheses as you do the number of 'IFs'." This is great.


    Kind regards,

    Irene

  • Hi,

    I have the same need, but this formula is not working for me, any thoughts? I keep getting "invalid operation"

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @carlosreyeskpqw841

    Can you post the formula that you're currently attempting to use, and a screen capture of your sheet (but block out sensitive data)?

    Thanks!

    Genevieve

  • Thanks Genevieve,

    This is what I'm using:

    =IF([Total Accrual %]@row = 1, "Green", =IF([Total Accrual %]@row = 0, "Red", "Yellow"))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @carlosreyeskpqw841

    It looks like you just have an extra = sign in there which is causing the error! 🙂

    Try this:

    =IF([Total Accrual %]@row = 1, "Green", IF([Total Accrual %]@row = 0, "Red", "Yellow"))


    Cheers,

    Genevieve

  • Bosakie
    Bosakie ✭✭

    I have the same issues, but for the expression I am using a number and if it is greater equal to 89 the result will be Dark Blue, the next if statement is greater equal to 78 it will be blue, or else Yellow.

    =IF([Effort]@row >= 89, "Dark Blue", IF([Effort]@row >= 78, Blue, "Yellow"))

    I get an unparseable error

    I want to continue the formula with additional nested if statements, but need to get the format of the nested if statement down first,


    Thanks in advance

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Bosakie

    It looks like you're just missing the quotes around "Blue" in the second statement:

    =IF([Effort]@row >= 89, "Dark Blue", IF([Effort]@row >= 78, "Blue", "Yellow"))

    Cheers,

    Genevieve

  • Please help, I feel like I am running into the same is above but am not finding the solution. I am trying to nest a Vlookup in an If statement. I was able to get it to pull from the first If but it ignoring the second rule.

    =IF([Manufacture Assigned]@row = "[contact 2]#", VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 13, false), VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 11, false))

    =IF([Manufacture Assigned]@row = "[contact 2]#", VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 13, false), IF([Manufacture Assigned]@row = "[Contact 1]#", VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 11, false))) This one is coming back Unparseable

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Jen Stover

    Can you share a screen capture of the sheet you're placing the formula in?

    Also, it looks like your ranges are quite large, if you're looking at the 13th column or the 11th column. I'd highly recommend using an INDEX(MATCH combination instead of VLOOKUP as this way you're only referencing TWO individual columns instead of a range across 13. Even though all the middle columns aren't necessary for the output, they're still being referenced which bumps up your Referenced Cell number as well as making the formula slower to load.

    Here's an example of an INDEX(MATCH

    =INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

    So in your case for your first VLOOKUP, you'd replace it with:

    =INDEX({Column to return}, MATCH([Location Number]@row, {Location Column Reference}, 0))


    Then the next thing I'd check in your formula is if you're looking for the text "[contact 2]#" or if you're trying to reference the Sheet Summary Field called contact 2.

    If you're referencing a Sheet Summary Field, you'll want to ensure there are no quotes around the reference.

    Try a full formula like this:

    =IF([Manufacture Assigned]@row = [contact 2]#, INDEX({Column to return}, MATCH([Location Number]@row, {Location Number Reference}, 0)), IF([Manufacture Assigned]@row = [Contact 1]#, INDEX({Different Column to return}, MATCH([Location Number]@row, {Location Number Reference}, 0))))


    See:

    Cheers,

    Genevieve

  • @Genevieve P. Thank you so much it was as simple as the quotes around the summary contact. Removed it and now everything works perfect!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!