If conditioning formula
I am trying to create a condition formula that yields red or green under the following conditions
If total A(forecast) is equal or higher than 10% of total b(budget) then red, less than 10% green.
However I am also dealing with negative growth compared to budget, I need to show show if the negative growth is between -1% to -9.999% green, greater than or equal to -10% red.
My current formula is =IF([Updated 2022 Forecast (CapEx Spent + New Forecast)]@row >= ([CapEx Budget 2022]@row * 1.1), "Red", "Green") which only account for the first part.
Any suggestions on integrating a condition for the negative growth greater or equal to -10%?
Thank you
Answers
-
You can add an OR Function to the beginning of your formula to check for your second condition as well. Try something like this:
=IF(OR(Forecast >= (Budget * 1.1), Forecast <= (Budget * -1.1)), "Red", "Green")
So for your formula:
=IF(OR([Updated 2022 Forecast (CapEx Spent + New Forecast)]@row >= ([CapEx Budget 2022]@row * 1.1), [Updated 2022 Forecast (CapEx Spent + New Forecast)]@row <= ([CapEx Budget 2022]@row * -1.1)), "Red", "Green")
Let me know if that works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!