0

I'm working on an Excel problem that populates a separate column based on conditional answers of multiple (3) yes/no columns. Specifically, I'm trying to work with a combination of options. Those 3 options:

  • cww
  • flex
  • tele

You may select either 1, 2 or all 3 of those options.

My worksheet:

My Screenshot

My code that inelegantly satisfies my problem.

=IF(AND(B2="NO",B2=C2,B2=D2),"NO-FLEX",IF(AND(B2="YES",B2=C2,B2=D2),"Full match",IF(AND(B2="Yes",C2="Yes"),"CWW-FLEX",IF(AND(B2="Yes",D2="Yes"),"CWW-TELE",IF(AND(C2="Yes",D2="Yes"),"FLEX-TELE",IF(AND(B2="NO",C2="NO"),"TELE-ONLY",IF(AND(B2="NO",D2="NO"),"FLEX-ONLY",IF(AND(C2="NO",D2="NO"),"CWW-ONLY",IF(AND(B2="YES",C2="",D2=""),"CWW-ONLY",IF(AND(C2="YES",B2="",D2=""),"FLEX-ONLY",IF(AND(D2="YES",B2="",C2=""),"TELE-ONLY",IF(AND(B2="NO",C2="YES",D2=""),"FLEX-ONLY",IF(AND(B2="NO",C2="",D2="YES"),"TELE-ONLY",IF(AND(D2="NO",B2="",C2="YES"),"FLEX-ONLY",IF(AND(B2="",C2="NO",D2="YES"),"TELE-ONLY",IF(AND(B2="YES",C2="",D2="NO"),"CWW-ONLY",IF(AND(B2="YES",C2="NO",D2=""),"CWW-ONLY",)))))))))))))))))

Thus far, I've managed to create a large IF(AND) statement that, for the most part, populates a column with the correct indicator based on the combination of selections.

This solution isn't elegant and there may be a way to simplify my growing nested IF(AND) formula. Can anyone help? Would greatly appreciate any advice.

1
  • I like your last edit ;-)
    – robinCTS
    Jun 13, 2018 at 14:32

1 Answer 1

1

I think the best way would be to use the CHOOSE() function:

=CHOOSE(1+1*(UPPER(B2)="YES")+2*(UPPER(C2)="YES")+4*(UPPER(D2)="YES"),"n/a","CWW-ONLY","FLEX-ONLY","CWW-FLEX","TELE-ONLY","CWW-TELE","FLEX-TELE","Full match")

I've set the output for the case where there are no YESs to n/a just so the formula is easier to understand. Replace the "n/a" in the formula with this:

CHOOSE(1+1*(UPPER(B2)="NO")+2*(UPPER(C2)="NO")+4*(UPPER(D2)="NO"),"n/a","n/a","n/a","TELE-ONLY","n/a","FLEX-ONLY","CWW-ONLY","NO-FLEX")

The combined formula will correctly detect any mixture of upper and lower case characters for NO and YES and treat everything else as a blank:

=CHOOSE(1+1*(UPPER(B2)="YES")+2*(UPPER(C2)="YES")+4*(UPPER(D2)="YES"),CHOOSE(1+1*(UPPER(B2)="NO")+2*(UPPER(C2)="NO")+4*(UPPER(D2)="NO"),"n/a","n/a","n/a","TELE-ONLY","n/a","FLEX-ONLY","CWW-ONLY","NO-FLEX"),"CWW-ONLY","FLEX-ONLY","CWW-FLEX","TELE-ONLY","CWW-TELE","FLEX-TELE","Full match")

Explanation:

Each of the two parts work like a binary digital multiplexer and the whole formula acts like a trinary multipler.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .