Health Ball Color Based on progress
Hello I have =IF(OR([COS 2022 ACT  YTD]@row > 0.05 + [COS 2021 Baseline]@row, "Green", IF([COS 2022 ACT  YTD]@row > 0.1 + [COS 2021 Baseline]@row, "Yellow", IF([COS 2022 ACT  YTD]@row > 0.15 + [COS 2021 Baseline]@row, "Red"))))
COS 2021 Baseline COS 2022 Target COS 2022 ACT  YTD
Can you please help me with this: over all planned improvement is .5 then by June it should have improved by 0.25 + 5% for green 0.25 + 10% Yellow and 0.25 + 15% Red
Best Answer

Hi @Daxal
There are a couple of details to adjust in your formula. When you use an OR statement, you'll want to make sure you close off the OR Function OR() < before then telling it what to do.
=IF(OR(This, or That), "Green"
However based on your description, it doesn't sound like we need an OR at all. You can simply state what you want the formula to look for, since there is only one value to search for.
The second detail to note is that you are adding 0.05 to a value but you're also looking to see if a cell is > than a value. There currently isn't a way for your formula to tell what you want to do first... the > or the +. See: Create and Edit Formulas in Smartsheet
Try wrapping your + statements in their own (parentheses), like so:
[COS 2022 ACT  YTD]@row > (0.05 + [COS 2021 Baseline]@row), "Green",
Finally, IF Function statements will stop as soon as they find a match. This means that if a cell is > 0.1 (greater than 0.1) it will also be > 0.05 (greater than 0.05), so we need to make sure we're putting the statements in the right order. Start with your 0.15, first:
Adjusted Formula:
=IF([COS 2022 ACT  YTD]@row > (0.15 + [COS 2021 Baseline]@row), "Red", IF([COS 2022 ACT  YTD]@row > (0.1 + [COS 2021 Baseline]@row), "Yellow", IF([COS 2022 ACT  YTD]@row > (0.05 + [COS 2021 Baseline]@row), "Green"))))
Does this give you the output you're looking for? If not, could you be more specific with what you want each statement to do?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Hi @Daxal
There are a couple of details to adjust in your formula. When you use an OR statement, you'll want to make sure you close off the OR Function OR() < before then telling it what to do.
=IF(OR(This, or That), "Green"
However based on your description, it doesn't sound like we need an OR at all. You can simply state what you want the formula to look for, since there is only one value to search for.
The second detail to note is that you are adding 0.05 to a value but you're also looking to see if a cell is > than a value. There currently isn't a way for your formula to tell what you want to do first... the > or the +. See: Create and Edit Formulas in Smartsheet
Try wrapping your + statements in their own (parentheses), like so:
[COS 2022 ACT  YTD]@row > (0.05 + [COS 2021 Baseline]@row), "Green",
Finally, IF Function statements will stop as soon as they find a match. This means that if a cell is > 0.1 (greater than 0.1) it will also be > 0.05 (greater than 0.05), so we need to make sure we're putting the statements in the right order. Start with your 0.15, first:
Adjusted Formula:
=IF([COS 2022 ACT  YTD]@row > (0.15 + [COS 2021 Baseline]@row), "Red", IF([COS 2022 ACT  YTD]@row > (0.1 + [COS 2021 Baseline]@row), "Yellow", IF([COS 2022 ACT  YTD]@row > (0.05 + [COS 2021 Baseline]@row), "Green"))))
Does this give you the output you're looking for? If not, could you be more specific with what you want each statement to do?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. Thank you for this, it works perfectly!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!