# 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",)))

• ✭✭✭✭✭✭

```=
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: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.

• ✭✭✭✭✭✭

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

• ✭✭
edited 03/15/22

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.

• ✭✭✭✭✭✭

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, andree@workbold.com)

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.

• ✭✭

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

• ✭✭✭✭✭✭

```=
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: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.

• ✭✭

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

Mike

• ✭✭✭✭✭✭

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: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!