Nested IF function using negative percent values & RYG balls

Options

 I'm using a nested IF statement to determine the colour of RYG balls in Column 'YTD Health', using data from an adjacent column of percentages. The percentages listed are a combination of negative and positive values.

What I want to happen is this: 

Values 0 to -40% = Yellow

Values less than -41% = Red

Values 0 and greater = Green

Anything else = Error

I know that because I'm referencing percentages that I need to use decimals, so my current IF statement in the 'YTD Health' column looks like this:  

=IF([YTD Position (Variance from target)]1 <= -0.4, "Yellow",

IF([YTD Position (Variance from target)]1 <= -0.41, "Red",

IF([YTD Position (Variance from target)]1 >= 0, "Green", "Error"))) 

It's working for most cells, but not all. E.g. -80% should be red...? See attached.


Tags:

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    With nested IF statements the first true statement met ends the logic chain, since -80% is less than -0.4., it will go yellow. You need to reorder to get the Red criteria first, and modify the condition for yellow to have it be less than 0. You also need to pick if you want Yellow or Green for values of 0, as written 0 will be yellow but you can change this by going from <=0 to just <0.

    =IF([YTD Position (variance from target)]@row <= -0.41, "Red", IF([YTD Position (variance from target)]@row <= 0, "Yellow", IF([YTD Position (variance from target)]@row >= 0, "Green", "Error")))


    Also, note that you will never get an error this way, at least based on my tests.


    If you want to check for Errors, which I assume in this case are non-numerical values, nest the IF statement within ISNUMBER as follows

    =IF(ISNUMBER([YTD Position (variance from target)]@row), IF([YTD Position (variance from target)]@row <= -0.41, "Red", IF([YTD Position (variance from target)]@row <= 0, "Yellow", IF([YTD Position (variance from target)]@row >= 0, "Green", "Error"))))


    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    Hi @Carly Boulton

    Your first IF statement is wrong, according to what you said you said <=-0.4. While your condition is between 0 and -0.4.

    I'd suggest you to reverse your formula to make it easier to read:

    =IFERROR(IF([YTD Position (Variance from target)]1 >= 0, "Green", IF([YTD Position (Variance from target)]1 <= -0.41, "Red", "Yellow")), "Error")

    Yellow will be your default value if it's not green or red. I've inserted the "Error" condition you returned within an IFERROR function, but I'm not sure that suits what you want to do here.


    Hope it helped.

  • Carly Boulton
    Options

    Thank you both. I have taken your advice and the colour balls are now correct - thank you so much! I am still having issues with displaying an Error message though. I have tried both suggestions above (ISNUMBER and IFERROR), but can't seem to get a result, only '#UNPARSEABLE'.

    Dan, Which example is best to take from above as the statement in your text differs from the screenshot...?

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    @Carly Boulton

    #UNPARSEABLE means Smartsheet cannot process your formula, usually that means there's a typo somewhere, or a parenthesis at the wrong place... a semi colon instead of a coma (or vice versa...)

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

    Hi @Carly Boulton

    Can you maybe share the formula that isn't working? That would make it easier to help.

    I hope that helps!

    Be safe and have a fantastic weekend!

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!