More than 30 logical expressions in AND -- Quandry

[sunny316]

Hello,

I have a formula which goes something like this

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",AND(A35="N",A36="N",A37="N",A38="N",A39="N",A40="N",A41="N",A42="N", A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N")),"ERROR: NOT identified !", ""))

now I wanted to expand it to

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",A35="N",A36="N",A37="N",A38="N",A39="N",A40="N",AND(A41="N",A42="N",A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N",A52="N",A53="N",A54="N",A55="N",A56="N",A57="N",A58="N",A59="N",A60="N")),"ERROR: Prohibited substance mismatch or NOT identified !", ""))

and it didn't work, says that formula is too long. I tried it this way

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",A35="N",A36="N",A37="N",A38="N",A39="N",A40="N")=AND(A41="N",A42="N",A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N",A52="N",A53="N",A54="N",A55="N",A56="N",A57="N",A58="N",A59="N",A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !", ""))

but that didn't give me the expected result.

I'm using Office 2003, please help me with getting around the stupid limitation that Microsoft placed on using just 30 expressions in a function in MS Excel 2003.

Thanks..appreciate any help.

 

[DonkeyOte]

Perhaps you might try:

=IF(D7="Yes/No?","Select Yes or No on Instruction Sheet",IF(AND(D7="Yes",COUNTIF(A18:A60,"<>N")=0),"ERROR: Prohibited substance mismatch or NOT identified !",""))

 

[Aladin Akyurek]

Concerning the first formula:

What do you want to return if D7 is Yes and all cells in A18:A51 are N?

 

[jim may]

Do you mean something like this? Commit to cell with ControlKey+ShiftKey+Enter,
not just Enter. {}'s should appear around below formula; {}'s cannot be entered by using keyboard.

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF(AND(D7="Yes",A18:A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !",""))

 

[sunny316]

Do you mean something like this? Commit to cell with ControlKey+ShiftKey+Enter,
not just Enter. {}'s should appear around below formula; {}'s cannot be entered by using keyboard.

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF(AND(D7="Yes",A18:A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !",""))

I am not aware of the {} option, can you please explain what it is and would it work any differently than the usual AND function in Excel 2003?

 

[shemayisroel]

I am not aware of the {} option, can you please explain what it is and would it work any differently than the usual AND function in Excel 2003?

This is an array formula

See below for a brief overview on arrays

http://www.cpearson.com/excel/ArrayFormulas.aspx

http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

 

[Aladin Akyurek]

I don't think an array formula is needed here...

 

[DonkeyOte]

Have you tried the COUNTIF suggestion ? No real need for an Array in this instance (at least not as I see it).

 

[sunny316]

Thanks all, I'll try both the COUNTIF and the Array options and see which works best for my sheet.

 

[shemayisroel]

I don't think an array formula is needed here...

Agree, just answering the OP question about arrays