Multiple IF with more than 1 boolean statement

Kym L

Hi I need to write a compound If statement with the following logic

If (cell) between 10 & 35 = Low

if cell is between 36 & 65 = Moderate

if cell is 66 to 100 = High

Kelly Moore

Hey @Kym L

=IF([your field]@row < 10, "", IF([your field]@row <= 35, "Low", IF([your field]@row <= 65, "Moderate", IF([your field]@row <= 100, "High"))))

you must change the [your field] to match your actual column name

Does this work for you?

Kelly

Kym L

Hi Kelly - thanks for your reply!

This isn't quite the logic we need.

If column is >=10 or <=35 Low

If column is>= 36 or <= 65 Moderate

If column is >=66 or <= 100 High

Kelly Moore

Hey Kym

Did you try it? An IF statement moves from left to right as it executes and it stops at the first True value it finds. As written Below 10,the field will be blank. Which means if it is above 10, it is moving into the next statement. Please try the formula and I'll fix any logic that isn't right. If you really want it just as you have written, I can add an IF/AND to every statement but it is not necessary.

Kym L

I did try it. This Formula is returning Low for any of the 3 ranges. I'm sure I am missing something

=IF([Score (Average / or / Total)]@row >= 10, "L", IF([Score (Average / or / Total)]@row <= 35, "M", IF([Score (Average / or / Total)]@row >= 36, "M", IF([Score (Average / or / Total)]@row <= 65, "M", IF([Score (Average / or / Total)]@row <= 100, "H")))))

Kym L

sorry - below is the formula I am using that's not working...

=IF([Score (Average / or / Total)]@row >= 10, "L", IF([Score (Average / or / Total)]@row <= 35, "L", IF([Score (Average / or / Total)]@row >= 36, "M", IF([Score (Average / or / Total)]@row <= 65, "M", IF([Score (Average / or / Total)]@row <= 100, "H")))))

Paul Newcome

@Kym L It looks like you may have adjusted the first bit of @Kelly Moore's formula. Try switching the first operator to just being less than (as opposed to greater than or equal to) and switch the first output to blank instead of "L" ("" ).

Kym L

Hi Paul - in Kelly's formula < 10 is incorrect, we want the expressions to evaluate the number is the cell as follows

10 <= x >=35

36 <= x >=65

66<=x <=100

Kym L

Paul - is the scenario that Kelly sent (when I change to greater than 10) all values are Low regardless of the number being evaluated. if I make the second argument "" all are blank. See the example below

=IF([Score (Average / or / Total)]@row >= 10, "L", IF([Score (Average / or / Total)]@row <= 35, "", IF([Score (Average / or / Total)]@row >= 36, "M", IF([Score (Average / or / Total)]@row <= 65, "M", IF([Score (Average / or / Total)]@row <= 100, "H")))))

Paul Newcome

Right. I Kelly's formula she says that if it is less than 10 then leave blank. The way a nested IF statement works is that it moves from left to right and stops on the first true value. So if it makes it to the second argument then the first must be false.

This means that if it makes it to less than or equal to 35 then it must be greater than or equal to 10 (because it was not less than otherwise it wouldn't have made it this far into the formula).

So Kelly's formula does work for what you have listed as your requirements so long as you maintain her operators and outputs. The first operator should be less than and the first output (not the second) should be blank.

Kelly's formula spelled out reads:

=IF([your field]@row < 10, "", IF([your field]@row <= 35, "Low", IF([your field]@row <= 65, "Moderate", IF([your field]@row <= 100, "High"))))

If the number is less than 10, output blank.

=IF([your field]@row < 10, "", IF([your field]@row <= 35, "Low", IF([your field]@row <= 65, "Moderate", IF([your field]@row <= 100, "High"))))

If the number is not less than 10 but is less than 35, output "Low".

=IF([your field]@row < 10, "", IF([your field]@row <= 35, "Low", IF([your field]@row <= 65, "Moderate", IF([your field]@row <= 100, "High"))))

If the number is not less than 10 and not less than 35 but is less than 65, output "Moderate".

=IF([your field]@row < 10, "", IF([your field]@row <= 35, "Low", IF([your field]@row <= 65, "Moderate", IF([your field]@row <= 100, "High"))))

If the number is not less than 10 and is not less than 35 and is not less than 65 but is less than 100, output "High".

So when reading from left to right and stopping on the first true value, the outputs would be as such:

x < 10 --> blank

10 <= x >= 35 --> "Low"

36 <= x >= 65 --> "Moderate"

66 <= x >= 100 --> "High"

Kym L

A team member figured it out

=IF(AND([Score (Average / or / Total)]@row > 9, [Score (Average / or / Total)]@row < 36), "L", IF(AND([Score (Average / or / Total)]@row > 35, [Score (Average / or / Total)]@row < 66), "M", IF(AND([Score (Average / or / Total)]@row > 65, [Score (Average / or / Total)]@row < 101), "H", "Unknown")))

Thanks Kelly and Paul!

Kym L

Ah - got it - thanks for taking the time!