Using IF(AND function with multiple conditions/criteria

KarenTF

So I'm trying to set a point value for when a selection is made (like metric scoring), but I have to assign two point-systems depending on what tier is answering, since the points will change depending on the tier. I was able to get the first selection to successfully calculate by:

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 79), 3, 5)

Since there are only two tiers, I was able to assign the 3pts for tier 1, and therefore my false value (for tier 2) would be 5pts. And this worked!

However, I need to finish the scale and add the rest of the points:

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 89), 5, 10)

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 99), 7, 15)

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row = 100), 10, 20)

But anytime I add any further functions, I get an error. I've tried without the (=), by adding in (,) in between functions, but I'm not having any luck. Does anyone have a suggestion?

JamesB

@KarenTF

To use nested IF Statements, either your True answer or your False Answer must be the start of the next IF statement.

Using the Statement below, you have completed the IF formula.

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 79), 3, 5)

To nest this statement it would look more like this...

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row = 100), 10, IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 99), 7, IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 89), 5, 10)))

This first IF says if Tier 1 and 100, then 10, otherwise move to the next IF statement.