Help with IF OR formula

[emmadisa]

I can't seem to resolve, would appreciate any help.

I'm looking to find a value between number ranges in a single cell. <=40, >=40 <=60, >=60 <=80, >=100. The for each result return TEXT: <=40 "Low", >=40 <=60 "MEDIUM", >=60 <=80 "HIGH", >=100 "CRITICAL"

current formula that's not working target cell is C3:

=IF(OR(C3<40,), "LOW", IF(OR(C3>=40,C3<=60),"MEDIUM", IF(OR(C3>=60, C3<=80), "HIGH", IF(OR(C3>=80,),"CRITICAL", ""))))

 

[Alex Blakenburg]

To do it they way you are doing it you need AND not OR.
You have some inconsistencies in you logic though.
• You are using both >= and <= so do you want 60 to be Medium or High based on Low being < 40 I have assumed that will apply to all.
• You have a gap from 80 - 100 where it is above high and below critical, what category is this.

You would normally use something like this and rely on the fact that IF works from left to right.

=IF(C3<40, "LOW", IF(C3<60,"MEDIUM", IF(C3<80, "HIGH", "CRITICAL")))

 

[Cubist]

Edit:
What Alex said.

To avoid the nested IF, the other typical approach is to do a lookup.

=XLOOKUP(C3,{0,40,60,80},{"LOW","MEDIUM","HIGH","CRITICAL"},,-1)

 

[emmadisa]

Thanks Alex. C3 receives a numeric score from another calculation. The score is related to the items criticality, is a number between 0-100. I want to calculate a text output based on the range:

Low = Lower than 40.
Medium = Equal to and higher than 40, equal to or lower than 60.
High = Equal to and higher than 61, equal to or lower than 80
Critical = equal to and Higher than 81

Regards,

Tony

=IF(C3<40, "LOW", IF(C3<60,"MEDIUM", IF(C3<80, "HIGH", "CRITICAL")))

 

[Alex Blakenburg]

I personally think that's inconsistent and would change the 40 threshold to 41 as well but this does what you said:

=IF(C3<40, "LOW", IF(C3<61,"MEDIUM", IF(C3<81, "HIGH", "CRITICAL")))

For Cubist formula follow the same logic.

 

[Cubist]

Since your intervals are increments of 20's you can try this as well:

=CHOOSE(MAX(C3/20,1),"LOW","MEDIUM","HIGH","CRITICAL","CRITICAL")