Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

IF formula with number range

6764 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Isabelle_Kirsch
5 - Automation Enthusiast
5 - Automation Enthusiast

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

4 Replies 4

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:

Screenshot 2020-02-25 at 13.19.22

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. :slightly_smiling_face: