Forum Discussion

Jessica12345's avatar
Jessica12345
Copper Contributor
Nov 15, 2023

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

 

 

Formulas and Functions

2 Replies

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Nov 16, 2023

    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")
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 15, 2023

    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.

Resources