How to write formula to display health based on baseline variance

fmorse_apothecary

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.

Eric Law

@fmorse_apothecary So it won't work for duration. So... I created a new formula and a helper column.

Risk/Health: =IF(ISBLANK([Var Link Int]@row), "Gray", IF([Var Link Int]@row >= 0, "Green", IF([Var Link Int]@row <= -10, "Red", "Yellow")))

Var Link Int: =VALUE(REPLACE([Var Link]@row, FIND("d", [Var Link]@row), 1, ""))

Hope that solves it! So, the only issue is if you don't have the d. If you don't you can add an IFERROR to the Var Link Int and the formula will be =IFERROR(VALUE(REPLACE([Var Link]@row, FIND("d", [Var Link]@row), 1, "")), VALUE([Var Link]@row))

Eric Law

@fmorse_apothecary

Try this =IF(ISBLANK([Var Link]@row), "Gray", IF([Var Link]@row >= 0, "Green", IF([Var Link]@row <= -10, "Red", "Yellow")))

fmorse_apothecary

@Eric Law Thank you for responding!

So I've got that formula entered, and it... kind of worked?

As long as the variable remains above or at 0, it shows green.

Any other variable below 0 shows yellow.

The only exception is when the variable is set to -1 and -10 exactly, then it'll show red.

Eric Law

@fmorse_apothecary Why do you have a d with the number?

fmorse_apothecary

@Eric Law The var column is set as a duration type column, which adds that extra letter pizazz in there, because it's needs to read a baseline variance that has either been manually entered or linked from a project timeline on a different sheet.

The two screenshots without the "d" were manually entered to see if I could get it to read "red" at all.

Is it my column type that's messing me up somehow?

But even as a text/number it doesn't want to read the variance:

And yes, I did double triple check that I had saved and refreshed between changing the column type. So it's not a misread because it hadn't updated yet.

Eric Law

@fmorse_apothecary So it won't work for duration. So... I created a new formula and a helper column.

Risk/Health: =IF(ISBLANK([Var Link Int]@row), "Gray", IF([Var Link Int]@row >= 0, "Green", IF([Var Link Int]@row <= -10, "Red", "Yellow")))

Var Link Int: =VALUE(REPLACE([Var Link]@row, FIND("d", [Var Link]@row), 1, ""))

Hope that solves it! So, the only issue is if you don't have the d. If you don't you can add an IFERROR to the Var Link Int and the formula will be =IFERROR(VALUE(REPLACE([Var Link]@row, FIND("d", [Var Link]@row), 1, "")), VALUE([Var Link]@row))

fmorse_apothecary

@Eric Law That's working! Thank you so much for helping me troubleshoot this!