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!