Multiple IF statement

[razormb]

Hi there,

I have a spreadsheet with 6 columns of data. If any of say A2, B2, C2, D2 or E2 equal y, then the 6th column will be n. However if A2 and B2 and C2 and D2 and E2 ALL equal n then the 6th column will be Y.

I have got the IF statement working for the first section:

=IF(A2="y","N",IF(B2="y","N",IF(C2="y","N",IF(D2="y","N",IF(E2="y","N","")))))

How do I incorporate the second part into the formula?

Thanks in advance.

 

[oldbrewer]

I would concatenate a2 b2 c2 d2 e2 there are only 32 permutations and use a lookup table to return the desired answer

 

[razormb]

Many thanks for replying. What would the formula be? I haven't used concatenate before

 

[oldbrewer]

y

y

n

y

y

answer28

nnnnn

answer1

nnnny

answer2

formula returning answer28 is

nnnyn

answer3

nnnyy

answer4

=VLOOKUP(A2&B2&C2&D2&E2,mytable,2)

nnynn

answer5

nnyny

answer6

nnyyn

answer7

nnyyy

answer8

nynnn

answer9

nynny

answer10

nynyn

answer11

nynyy

answer12

nyynn

answer13

nyyny

answer14

nyyyn

answer15

nyyyy

answer16

ynnnn

answer17

ynnny

answer18

ynnyn

answer19

ynnyy

answer20

ynynn

answer21

ynyny

answer22

ynyyn

answer23

ynyyy

answer24

yynnn

answer25

yynny

answer26

yynyn

answer27

yynyy

answer28

yyynn

answer29

yyyny

answer30

yyyyn

answer31

yyyyy

answer32

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[RonB1111]

Try:

=IF(AND(A2="y",B2="y",C2="y",D2="y",E2="y"),"n",IF(AND(A2="n",B2="n",C2="n",D2="n",E2="n"),"y",""))

 

[razormb]

Thanks Ronb and oldbrewer.

Ronb - Yours works if A2 thru to E2 all have data in the cells. However, it could be that just say A2 has an "n" in it. Your formula just doesn't show anything.

Thanks in advance

 

[RonB1111]

This will ignore blanks,

=IF(AND(OR(A2="y",A2=""),OR(B2="y",B2=""),OR(C2="y",C2=""),OR(D2="y",D2=""),OR(E2="y",E2="")),"n",IF(AND(OR(A2="n",A2=""),OR(B2="n",B2=""),OR(C2="n",C2=""),OR(D2="n",D2=""),OR(E2="n",E2="")),"y",""))

 

[ukmikeb]

Hi

Try :-

=IF(OR(A2:E2="n"),"Y",IF(SUM(--(A2:E2="Y"))=5,"n",""))

entered with Control-Shift-Enter

hth

 

[RonB1111]

This will ignore blanks,

=IF(AND(OR(A2="y",A2=""),OR(B2="y",B2=""),OR(C2="y",C2=""),OR(D2="y",D2=""),OR(E2="y",E2="")),"n",IF(AND(OR(A2="n",A2=""),OR(B2="n",B2=""),OR(C2="n",C2=""),OR(D2="n",D2=""),OR(E2="n",E2="")),"y",""))

This shortened formula will also ignore blanks:

=IF(AND(COUNTIF(A2:E2,"n")>0,COUNTIF(A2:E2,"y")=0),"y",IF(AND(COUNTIF(A2:E2,"y")>0,COUNTIF(A2:E2,"n")=0),"n",""))

 

[razormb]

Many, many thanks for your help. It is much appreciated