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

MikeRoss

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

Andrée Starå

@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?

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!

MikeRoss

@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å

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

MikeRoss

@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å

@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?

MikeRoss

@Andrée Starå

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

Mike

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!