If / Or Formula with Many Potential Outcomes

aecross

Hi there -

Trying to create an "if" formula that has an "or" statement built within. Basically, if the tier is 1, partner is TB, and CDs is SK, it should equal 100. But if the tier is 2, partner is TB, and CDs is SK, it should be 90. I have more options to add to this (another tier level and other partners), so it isn't just an "otherwise" statement. I have about 10 different possible outcomes based on tier / partner / CDs.

=IFERROR(IF([OVERALL TIER]@row = "Tier I", IF([PARTNER]@row = "TB", IF([CDs]@row = "SK", "100")))), OR(IF([OVERALL TIER]@row = "Tier II", IF([PARTNER]@row = "TB", IF([CDs]@row = "SK", "90"))))

Any help is appreciated!

Frank B.

Hello @aecross try this:

=IF(

 AND([OVERALL TIER]@row = "Tier I", [PARTNER]@row = "TB", [CDs]@row = "SK"),

 100,

 IF(

  AND([OVERALL TIER]@row = "Tier II", [PARTNER]@row = "TB", [CDs]@row = "SK"),

  90,

  "Other value"

 )

)

In this formula, the first condition checks if the overall tier is "Tier I", the partner is "TB", and the CDs are "SK". If all these conditions are true, it returns the value 100. Otherwise, it moves on to the next condition.

The second condition checks if the overall tier is "Tier II", the partner is "TB", and the CDs are "SK". If these conditions are true, it returns the value 90. If none of the conditions match, you can specify the desired outcome for other scenarios by replacing "Other value" with the appropriate value or expression.

You can continue adding more conditions by extending the formula within the nested IF statements.

HTH!