IF statement producing unusual results?
Posted by Victoria Bailey on January 03, 2002 7:19 AM
Below is a copy of an IF statement which is producing unusual results:
=IF((C:C=L=L)*(F:F=O:O)*(B:B=K:K)*(D:D=M:M),"check","OK")
The IF statement compares first product codes in two columns, and then compares batch code in two columns, then location in two columns and finally quantity in two columns if there is a match then output OK else output Check.
However, I thought this worked but someone pointed out that it is only by chance that it is outputting the correct result as above, in fact it is sometimes saying to check a row when it is actually OK.
Can you help?
Thank you in advance.
Posted by Scott on January 03, 2002 7:43 AM
Try this:
=IF((AND(C:C=L:L,F:F=O:O,B:B=K:K,D:D=M:M))=TRUE,"OK","CHECK")
It should return OK if all arguments are true.
Posted by Paul N on January 03, 2002 7:43 AM
Clarification needed for IF statement...
Voctoria, you say "if there is a match" Are you saying, if C=L, or F=O, or B=K then it is OK? Or do they all have to match for the OK to appear?
Posted by Paul N on January 03, 2002 7:48 AM
If Statement....
If ALL must equal to display "OK", use the following:
=IF(AND((C:C=L:L),(F:F=O:O),(B:B=K:K),(D:D=M:M)),"OK","CHECK")
if you want OK to appear if ANY or ALL of your comparisons match use the following:
=IF(OR((C:C=L:L),(F:F=O:O),(B:B=K:K),(D:D=M:M)),"OK","CHECK")
Posted by Aladin Akyurek on January 03, 2002 8:17 AM
Victoria --
I'd rather see the first say 5 rows of each column you're comparing with each other. Care to post them?
Aladin
======== The IF statement compares first product codes in two columns, and then compares batch code in two columns, then location in two columns and finally quantity in two columns if there is a match then output OK else output Check.
