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
Check out the Formula Handbook template!