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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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 TotalSUMTicket sum]@row >= 42), "5 Star", IF(OR(AND(Threshold@row >= "0.11", Threshold@row < "0.2"), [Grand TotalSUMTicket 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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!