IF formula with number range

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

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.

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

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.