Nested 4 IFs

[hsandeep]

4 columns (AR, AS, BD & BE) contains either 0 or 1. Output (column CA) is required based on combination/(s).
If
AR35=1 AS35=1 BD35=1 BE35=0 Then CA35 should give 1
AR35=0 AS35=1 BD35=1 BE35=0 Then CA35 should give 2
AR35=0 AS35=1 BD35=1 BE35=0 Then CA35 should give 3
AR35=1 AS35=1 BD35=1 BE35=1 Then CA35 should give 4

What formula needs to be filled in CA35?
Thanx in advance.

 

[vds1]

Your second and third condition look same.

 

[Aladin Akyurek]

4 columns (AR, AS, BD & BE) contains either 0 or 1. Output (column CA) is required based on combination/(s).
If
AR35=1 AS35=1 BD35=1 BE35=0 Then CA35 should give 1
AR35=0 AS35=1 BD35=1 BE35=0 Then CA35 should give 2
AR35=0 AS35=1 BD35=1 BE35=0 Then CA35 should give 3
AR35=1 AS35=1 BD35=1 BE35=1 Then CA35 should give 4

What formula needs to be filled in CA35?
Thanx in advance.

=MATCH(AR35&AS35&BD35&BE35),{"1110","0110","0110","1111"},0)

The red entry and the entry before are the same!... Adjust to suit.

 

[hsandeep]

=MATCH(AR35&AS35&BD35&BE35),{"1110","0110","0110","1111"},0)

The red entry and the entry before are the same!... Adjust to suit.

2nd line should read as: AR35=0 As35=1 BD35=1 BE35=1 Then CA35 should give 2
I changed to 0111 instead of 0110 in Aladin's formula but the excel is not accepting the formula. Formula gets highlighted at BE35.

 

[hsandeep]

How to get 1 or 2 or 3 or 4 in CA35? based on above 4 criterias.

 

[Peter_SSs]

Try this adjustment to Aladin's previous formula.

=MATCH(AR35&AS35&BD35&BE35,{"1110","0111","0110","1111"},0)

 

[vds1]

Adjustments to Aladin Formula..

=MATCH((AR35&AS35&BD35&BE35)*1,{1110,111,110,1111},0)

 

[hsandeep]

=MATCH(AR35&AS35&BD35&BE35,{"1110","0111","0110","1111"},0)[/QUOTE]
This WORKS now.

 

[Aladin Akyurek]

=MATCH(AR35&AS35&BD35&BE35,{"1110","0111","0110","1111"},0)

This WORKS now.[/QUOTE]

Of course...

 

[excellent12]

Another solution:
=IF((AR35&AS35&BD35&BE35)={"1110"},1,IF((AR35&AS35&BD35&BE35)={"0110"},2,IF((AR35&AS35&BD35&BE35)={"0111"},3,IF((AR35&AS35&BD35&BE35)={"1111"},4,"N/A"))))

Or if you want a shorter foruma, the constraint is that there should always be a one of the 4 values in the cells:
=IF(AR35=0;IF(BE35=0;2;3);IF(AR35=1;IF(BE35=1;4;1))