IF/OR/AND not progressing
Hi, i am working on an IF statement and am going round in circles. my statement seems to get as far as 3 star in the statement and no further even though the criteria says it should.
For 5 star and 4 star it references a different column to count if the ticket number is above 50. Do I need an esle???
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(OR(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", AND(IF(OR(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star"))))))
Why is this and what am i doing wrong?
Thanks!!
Best Answer
-
Hi @drweclare
What your formula is staying is
If this is true
Threshold < 0.1 AND Grand Total >=42
Then 5 star.
If that is false, then continue. If this is true
Threshold >= 0.11 AND< 0.2
OR this is true
Grand Total >=50
Then 4 star. (possible problem 1, see below (later - it isn't the main issue))
If that is false, then continue. If this is true
Threshold >= 0.21
OR this is true
Threshold < 0.3
Then 3 star. If that is false, then continue.
However, there is no other possible situations, so nothing to continue with. At this point any row that wasn't already 5 or 4 star will be 3 star if the threshold is more than or equal to 0.21 or less than 0.3. That is every row.
Did you mean for this to be an AND as in the 4 star logic? The threshold needs to be between 0.21 and 0.3 to be 3 star? If so, we need to change the OR to and AND as shown in bold
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(AND(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", AND(IF(OR(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star"))))))
Now the formula can progress further, however, it fails, as I think you must have been playing around with the last bit while trying to make it work. The AND in bold here will cause an INVALID DATA TYPE error. You need to start this part with the IF, and your probably want to use AND again instead of OR.
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(AND(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", AND(IF(OR(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star"))))))
If you amend this to:
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(AND(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", IF(AND(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star")))))
I think this will do what you want.
With the exception of possible problem 1: The first IF will already have added 5 stars to any rows with a threshold <0.1 and a grand total above 42. So any rows here that have a threshold less than 0.1 will already be 5 stars. This might not be an issue.
Answers
-
Hi @drweclare
What your formula is staying is
If this is true
Threshold < 0.1 AND Grand Total >=42
Then 5 star.
If that is false, then continue. If this is true
Threshold >= 0.11 AND< 0.2
OR this is true
Grand Total >=50
Then 4 star. (possible problem 1, see below (later - it isn't the main issue))
If that is false, then continue. If this is true
Threshold >= 0.21
OR this is true
Threshold < 0.3
Then 3 star. If that is false, then continue.
However, there is no other possible situations, so nothing to continue with. At this point any row that wasn't already 5 or 4 star will be 3 star if the threshold is more than or equal to 0.21 or less than 0.3. That is every row.
Did you mean for this to be an AND as in the 4 star logic? The threshold needs to be between 0.21 and 0.3 to be 3 star? If so, we need to change the OR to and AND as shown in bold
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(AND(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", AND(IF(OR(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star"))))))
Now the formula can progress further, however, it fails, as I think you must have been playing around with the last bit while trying to make it work. The AND in bold here will cause an INVALID DATA TYPE error. You need to start this part with the IF, and your probably want to use AND again instead of OR.
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(AND(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", AND(IF(OR(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star"))))))
If you amend this to:
=IF(AND(Threshold@row < "0.1", [Grand Total-SUM-Ticket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand Total-SUM-Ticket sum]@row >= 50), "4 Star", IF(AND(Threshold@row >= "0.21", Threshold@row < "0.3"), "3 Star", IF(AND(Threshold@row >= "0.3", Threshold@row < "0.5"), "2 Star", IF(Threshold@row > "0.51", "1 Star")))))
I think this will do what you want.
With the exception of possible problem 1: The first IF will already have added 5 stars to any rows with a threshold <0.1 and a grand total above 42. So any rows here that have a threshold less than 0.1 will already be 5 stars. This might not be an issue.
-
Thank you for that, I tinkered a little more but what you shared helped me understand the problem. Thank you!
-
Happy to help @drweclare
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!