Negative Values corrupting integrity of Symbol Display Formulas

Chris Topher

I've got a sheet that is setup with a "Synergy Savings Risk" column with a formula to show the red/yellow/green status based on the "Synergy $ Performance %" which is a formula comparing "Synergy/Dis-Synergy Actual $" column with "Synergy/Dis-Synergy Target $". 

I have a problem in that when the "Synergy/Dis-Synergy Actual $" & "Synergy/Dis-Synergy Target $" holds negative values it skews the red/yellow/green statuses in the "Synergy Savings Risk" column. In the red boxes below you'll see an actual of -$222 compared to target, which is in fact favorable' less money spent, right? as such the "Synergy Savings Risk" should be displaying green, however it is showing yellow.

Can anyone advise how to resolve this or workaround for it?

Paul Newcome

Your actual is a negative which is good. Yes. But it isn't as far into the negative as your target. Technically you just missed target, so I would think the Yellow would be correct.

If you wanted negative actual to be green though, you would need to incorporate an OR statement into the Green portion to say that if the percentage is greater than or equal to 1 OR the actual is less than zero then output Green.

Chris Topher

Paul, as always thanks for your quick response, we'll give that a try!

Paul Newcome

Happy to help. 👍️