Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula for Multiple Outcomes

Copper Contributor

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

@Jessica12345 

=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.

if.png

@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")