How to write formula to display health based on baseline variance
Hello! I'm trying to write a formula that looks at the baseline variance and displays the health status based on greater than - less than criteria.
We need the formula to read if variance is greater than or equal to "0", "green", if the variance is less than 0 but more than or equal to -9, "yellow" and finally if the variance is less than or equal to -10, "red"
This is my formula as of right now, I've gone through so many iterations at this point I'm starting to lose sense of the code;
=IF(ISBLANK([Var Link]@row), "Gray", IF([Var Link]@row > 0, "Green", IF([Var Link]@row < -9, "Red", IF([Var Link]@row < 0, "Yellow", "ERR"))))
This is another version of the formula I tried and failed:
=IF(ISBLANK([Var Link]@row), "Gray", IF([Var Link]@row >= 0), "Green", IF([Var Link]@row < -10), "Red", IF(AND([Var Link]@row > -9, [Var Link]@row < 0), "Yellow"))))
I have tried rearranging the order by having it look for red first, then yellow then green, green then yellow then red, red then green then yellow, etc.
I've tried nesting AND statements, OR statements, IF(OR(AND and IF(AND(OR statements, and I've even tried using IF(NOT.
At one point or another, the solutions above would seemingly work, as long as I didn't touch them. As I flipped the var column to 1, then -1, then 3, then -10, then 0, etc, it always gets stuck on red or yellow. It will flip back to green, or grey, but if I try to test the red or yellow call out, it would return "ERR" or whichever of the 2 warning colors it got stuck on.
This is regardless of saving & refreshing between changes to the variables, I've done it both ways multiple times and it always breaks.
Note: I've had the Var Link column set as a simple text/number, but then it ignores the data if it's linked from a true variance column, so I set it as a "duration" column, but it still breaks.
I'm sure I'm making the silliest error out there but I just can't see it and it's driving me nuts.
Any help is much appreciated, I see a lot of success in these health columns when using percentages but unfortunately that just does not suit our use case in this scenario.
Help Article Resources
Check out the Formula Handbook template!