Need help on efficient way to use IF formula

Vickie_Lim1 · ‎Jul 24, 2020

Hi there,

I wanted to create a formula for a field with 15 variables. See table below. Basically, is % complete + status is what’s on the corresponding column, it updates the “new stat” field.

Tried using this, but wonder if there’s a more efficient way than using ‘IF’ statement 15 times.

IF(AND({% Complete}=0, Stat=‘red’), ‘red’,
IF(AND({% Complete}=0,Stat=‘white’), ‘white’))

Justin_Barrett · ‎Jul 24, 2020

There is. Here’s what I do when looking at stuff like this: I look for patterns, and then figure out how to work with those patterns in the simplest way possible.

The first pattern I see is with your {% Complete} values. You essentially have only three options: 0, 100, or somewhere in between. To work with those three, we can use a SWITCH() function. Here’s the basic format:

SWITCH(
    THING_TO_COMPARE,
    OPTION_1, OUTPUT_1,
    OPTION_2, OUTPUT_2,
    ...
    OPTIONAL_FALLBACK_OUTPUT
)

In this case, we can look for 0 and 100 as specific options, and fall back to a different comparison scheme for everything else.

0% is really easy: if it’s not green, we pass it through; otherwise it’s an error.
100% is similarly easy: if it’s not green, it’s an error. Otherwise we keep the green.

Starting with just that setup, here’s what we have:

SWITCH(
    {% Complete},
    0, IF(Stat != "green", Stat, "error"),
    100, IF(Stat != "green", "error", Stat)
)

For values between 0 and 100, I see another pattern: yellow and red pass through untouched, while the others have specific options. For that, we can use another SWITCH() on the status. Here’s the full formula with that added:

SWITCH(
    {% Complete},
    0, IF(Stat != "green", Stat, "error"),
    100, IF(Stat != "green", "error", Stat),
    SWITCH(
        Stat,
        "white", "error",
        "orange", "yellow",
        "green", "error",
        Stat
    )
)

NOTE: If your {% Complete} returns a decimal value (between 0 and 1) instead of a whole number (between 0 and 100), use this formula:

SWITCH(
    {% Complete} * 100,
    0, IF(Stat != "green", Stat, "error"),
    100, IF(Stat != "green", "error", Stat),
    SWITCH(
        Stat,
        "white", "error",
        "orange", "yellow",
        "green", "error",
        Stat
    )
)

Vickie_Lim1 · ‎Jul 25, 2020

Thanks @Justin_Barrett. You are my knight today. I thought about this the whole day yesterday and it gave me a headache. THANKS AGAIN!!!