# 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)

• 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.

•  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

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!