# Need help on efficient way to use IF formula

Topic Labels: Formulas
1269 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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’))

2 Replies 2
18 - Pluto

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
)
)
``````
5 - Automation Enthusiast

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