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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thank you for this, it works perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!