How do I display a different symbol based on multiple values?

I've searched the community and tried multiple formulas but I cannot seem to get this scenario to work.

I have a '% Not Achieved' summary field, and depending on the % I want Green, Yellow or Red to display.

Scenario:

0% = Green

1% - 10% = Yellow

11% and over = Red

This is the formula I'm currently using. I get the Green and Yellow to show but not the Red. What am I doing wrong?


=IF(OR([% Not Achieved]# = 0), "Green", IF(OR([% Not Achieved]# >= 1, [% Not Achieved]# <= 10), "Yellow", IF(OR([% Not Achieved]# >= 11), "Red",)))

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @MikeRoss

    Try something like this instead.

    =
    IF([% Not Achieved]# = 0, "Green", 
    IF(AND([% Not Achieved]# >= 0.01, [% Not Achieved]# <= 0.1), "Yellow", 
    IF([% Not Achieved]# >= 0.11, "Red")))
    

    Did that work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

Answers

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

    Hi @MikeRoss

    I hope you're well and safe!

    Try something like this.

    =
    IF(OR([% Not Achieved]# = 0), "Green", 
    IF(OR([% Not Achieved]# >= 0.1, [% Not Achieved]# <= 0.1), "Yellow", 
    IF(OR([% Not Achieved]# >= 0.11), "Red",)))
    

    Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1. You'd need to use decimal values instead for it to work 

    25% = 0,25 (0.25)

    50% = 0,5 (0.5)

    100% = 1

    Depending on your country/region, you'll need to exchange the comma for a period.

    Did that work/help?

    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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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: [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.

  • MikeRoss
    MikeRoss
    edited 03/15/22

    @Andrée Starå

    Thanks for the quick follow up.

    I get the Green at 0 and Yellow for anything else but Red does not show when 0.11 or above.


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

    @MikeRoss

    Happy to help!

    Yes, I can see some errors in the formula.

    I'd be happy to take a quick look.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

  • @Andrée Starå

    In the sheet I'm counting the number of parent targets and then I'm basically calculating the % of completed targets. In the summary I'm counting the number of targets that are flagged as 'Not Achieved' and then in the '% Not Achieved' summary I'm dividing the Not Achieved total by the count of parent targets.

    Based on the % Not Achieved figure I assign the RYG symbol.

    Mike

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @MikeRoss

    Try something like this instead.

    =
    IF([% Not Achieved]# = 0, "Green", 
    IF(AND([% Not Achieved]# >= 0.01, [% Not Achieved]# <= 0.1), "Yellow", 
    IF([% Not Achieved]# >= 0.11, "Red")))
    

    Did that work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

  • @Andrée Starå

    That worked. You're a star. Thank you so much. What was I doing wrong?

    Mike

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

    @MikeRoss

    Excellent!

    You're more than welcome!

    You used the OR function but didn't have an OR option in the first part, and then you used the OR in the second part correctly, but it should be an AND instead because you wanted to check if it's between 1-10. Then you used the OR again for the last part, and it should only be a regular IF function.

    + the percentage issue.

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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: [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.