# Formula for Multiple Outcomes

Copper Contributor

# Formula for Multiple Outcomes

I am looking to create a formula between two cells that can have a variety of combinations. I would want the third column to populate with one of the three options (Exceeding, Delivering, Missing). Does anyone have any tips on what formula could be used in this situation?

If Exceeding & Exceeding, Then Exceeding

If Delivering & Exceeding, Then Delivering

If Delivering & Delivering, Then Delivering

If Delivering & Missing, Then Missing

If Missing & Missing, Then Missing

If Exceeding & Missing, Then Missing

2 Replies

# Re: Formula for Multiple Outcomes

=IF(AND(A1="Exceeding",B1="Exceeding"),"Exceeding",

IF(AND(A1="Delivering",B1="Exceeding"),"Delivering",

IF(AND(A1="Delivering",B1="Delivering"),"Delivering",

IF(AND(A1="Delivering",B1="Missing"),"Missing",

IF(AND(A1="Missing",B1="Missing"),"Missing",

IF(AND(A1="Missing",B1="Missing"),"Missing",""))))))

You can use a nested IF formula. With recent versions of Excel you can use IFS as well.

# Re: Formula for Multiple Outcomes

@Jessica12345 Assuming the results will be the same regardless of order (ie: Exceeding/Delivering is treated the same as Delivering/Exceeding), you can achieve the desired results with two logical tests. For example:

``=IF(OR(A1="Missing", B1="Missing"), "Missing", IF(OR(A1="Delivering", B1="Delivering"), "Delivering", "Exceeding"))``

In order to handle blank cells, though, a third logical test would be required. For example, to return "Incomplete" if either one or both of the cells are blank, use the following:

``=IF(OR(ISBLANK(A1), ISBLANK(B1)), "Incomplete", IF(OR(A1="Missing", B1="Missing"), "Missing", IF(OR(A1="Delivering", B1="Delivering"), "Delivering", "Exceeding")))``

Alternatively, you also could try the IFS function, if it's available in your version of Excel:

``=IFS(OR(ISBLANK(A1), ISBLANK(B1)), "Incomplete", OR(A1="Missing", B1="Missing"), "Missing", OR(A1="Delivering", B1="Delivering"), "Delivering", TRUE, "Exceeding")``