HI @Isabelle_Kirsch - this is happening because the statements are evaluated from left to right and when a condition is found that is true, it stops (and doesn’t evaluate any conditions beyond that). So in your case, as a score of 3, say, is less than 12, this is evaluated as true and shows orange. What you need to do is order the conditions in reverse order - something like this:
IF(
{Risk Score} < 6,
"🔋",
IF(
{Risk Score} < 12,
"📒",
IF(
{Risk Score} >= 12,
"🔴",
"🔋"
)
)
)
This will give you:
JB
HI @Isabelle_Kirsch - this is happening because the statements are evaluated from left to right and when a condition is found that is true, it stops (and doesn’t evaluate any conditions beyond that). So in your case, as a score of 3, say, is less than 12, this is evaluated as true and shows orange. What you need to do is order the conditions in reverse order - something like this:
IF(
{Risk Score} < 6,
"🔋",
IF(
{Risk Score} < 12,
"📒",
IF(
{Risk Score} >= 12,
"🔴",
"🔋"
)
)
)
This will give you:
JB
Your nesting is backwards. The second IF is triggering a True statement and therefore the final IF is never processed. You could change it to this:
IF({Risk Score} >= 12, “ :red_circle: ”, IF({Risk Score} < 6, “ :battery: ”,IF({Risk Score} < 12, “ :ledger: ”)))
But, this is a perfect scenario for a SWITCH statement
Because you have only three situations, you only need two IF statements. The else part of the formula takes into account the third situation.
IF( {Risk Score} < 6,
"🔋",
IF( {Risk Score} < 12,
"📒",
"🔴"
)
)
When building nested IF statements for a range of values, nest the values in order (always low to high, or always high to low) and use the same comparison operator throughout. Don’t mix < and > in the nesting.
- If you start at the low end of the range, use
<, <=, =.
- If you start at the high end of the range, use
>, >=, =.
The above formula assumes that the value will never be below 1 and never above 25. If you want a formula that will consider those cases, you need more nesting.
IF( {Risk Score} < 1,
"error, value too low",
IF( {Risk Score} < 6,
"🔋",
IF( {Risk Score} < 12,
"📒",
IF( {Risk Score} <= 25,
"🔴",
"error, value too high"
)
)
)
)
A SWITCH statement would be useful if you were considering exact scores, rather than a range of scores. Because a SWITCH statement requires explicitly stating all possible input values, it would require a large number of cases for the range and won’t work at all for decimal values. Thus, in this case, the nested IF statements are a bit cleaner.
HI @Isabelle_Kirsch - this is happening because the statements are evaluated from left to right and when a condition is found that is true, it stops (and doesn’t evaluate any conditions beyond that). So in your case, as a score of 3, say, is less than 12, this is evaluated as true and shows orange. What you need to do is order the conditions in reverse order - something like this:
IF(
{Risk Score} < 6,
"🔋",
IF(
{Risk Score} < 12,
"📒",
IF(
{Risk Score} >= 12,
"🔴",
"🔋"
)
)
)
This will give you:
JB
Thank you so much! it worked. So helpful. :slightly_smiling_face:
