Using IF(AND function with multiple conditions/criteria
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?
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!