If Function - Nested - Negative Percentage - Symbols

Erica Maynard

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)

Carson Penticuff

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

 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.

Ray Lindstrom

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

Erica Maynard

@Carson Penticuff, you are right it is actually =[Var to Budget]@row / [23 Budget MTD]@row

Erica Maynard

@Ray Lindstrom - I still get a #DivideByZero error

Carson Penticuff

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.

Erica Maynard

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

Carson Penticuff

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.

Erica Maynard

@Carson Penticuff, making it a column formula worked. Thank you for the suggestion.

Carson Penticuff

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!