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’))
+20There 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
)
)
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
)
)
Thanks @Justin_Barrett. You are my knight today. I thought about this the whole day yesterday and it gave me a headache. THANKS AGAIN!!!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
