Multiple IF with more than 1 boolean statement
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
Answers
-
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
-
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
-
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.
-
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")))))
-
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")))))
-
@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" ("" ).
-
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
-
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")))))
-
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"
-
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!
-
Ah - got it - thanks for taking the time!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!