IF formula with number range

[Isabelle_Kirsch]

Good morning,

I need to have a formula for my risk register which is based on the risk score with a number range.

If score is:
1-5: color is green
6-11: color is orange
12-25: color is red

I’ve come up with the following formula which technically works, except the 1-5 risk score number is appearing in orange instead of green on my table. Can someone help me correct what’s wrong?

IF({Risk Score} >= 12, “ :red_circle: ”, IF({Risk Score} < 12, “ :ledger: ”, IF({Risk Score} < 6, “ :battery: ”, “ :battery: ”)))

Thank you!

Isabelle

[JonathanBowen]

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

[RJ_Martino]

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

[kuovonne]

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.

[Isabelle_Kirsch]

Thank you so much! it worked. So helpful. :slightly_smiling_face: