Forum Discussion
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). D...
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")