# 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, ββ, IF({Risk Score} < 12, ββ, IF({Risk Score} < 6, ββ, ββ)))

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, ββ, IF({Risk Score} < 6, ββ,IF({Risk Score} < 12, ββ)))

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.

1 Like

Thank you so much! it worked. So helpful.

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