More than 30 logical expressions in AND -- Quandry

sunny316

New Member
Joined
Apr 18, 2010
Messages
7
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Concerning the first formula:

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

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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.


Code:
=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

New Member
Joined
Apr 18, 2010
Messages
7

ADVERTISEMENT

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.


Code:
=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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Have you tried the COUNTIF suggestion ? No real need for an Array in this instance (at least not as I see it).
 

sunny316

New Member
Joined
Apr 18, 2010
Messages
7
Thanks all, I'll try both the COUNTIF and the Array options and see which works best for my sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,625
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top