If Function - Nested - Negative Percentage - Symbols
I have a formula that is driving me insane. I am trying to get symbols to change color based on the % to Plan column (see screenshot). If the % is positive, I want the On Track Column to show Green. If it is negative BUT greater than -25%, I would like to show Yellow and all others to show Red. The problem is I am getting a #Divide By Zero error. (FYI, the % to Plan is a formula, it is =[Actual 2023]@row - [23 Budget MTD]@row)
Best Answer
-
This situation would be appropriate for a column formula. I would try pasting Ray's formula from above into the first cell of the column and then converting to a column formula.
Answers
-
the % to Plan is a formula, it is =[Actual 2023]@row - [23 Budget MTD]@row)
Can you double check that formula? The math doesn't seem to check out on this, and it would not result in percentages.
-
Hello,
I would go with something like the following.
If([% to Plan]@row > 0, “Green”, if(([% to Plan]@row > -.25, “Yellow”, “Red”))
If greater than 0, then Green
If greater than -.25, then yellow (anything greater than 0 would have already been caught and broke the if sequence)
Everything "Else", Red
-
@Carson Penticuff, you are right it is actually =[Var to Budget]@row / [23 Budget MTD]@row
-
@Ray Lindstrom - I still get a #DivideByZero error
-
Is this formula setup as a column formula? What you show should not be able to produce a divide by zero error, as there is no division in the formula itself.
The post above by @Ray Lindstrom shows the formula you should use, but I still have no explanation for how that error can be produced with the formula you posted.
-
@Carson Penticuff , that is why it is making me crazy because I agree it should not be producing that error. It is not set up as a column formula; I don't know what else to do; I don't want to have to manually change these to the appropriate color each month when the data gets updated, but I might just have to.
-
This situation would be appropriate for a column formula. I would try pasting Ray's formula from above into the first cell of the column and then converting to a column formula.
-
@Carson Penticuff, making it a column formula worked. Thank you for the suggestion.
-
My guess is that there may have been a typo in one or more cells. Setting it as a column formula ensures they are all the same and saves the time and effort of going cell by cell to spot a difficult to detect mistake. I'm glad its fixed!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!