IF/OR/AND not progressing

drweclare

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!!

KPH

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.

KPH

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.

drweclare

Thank you for that, I tinkered a little more but what you shared helped me understand the problem. Thank you!

KPH

Happy to help @drweclare