Excel 2013 Formula IF with AND, OR, and NOT, or may conditional formatting

[BGDunbar]

I understand how to use a nested IF statement in Excel and I understand how to use one of the three conditions (AND, OR, NOT) individually in a formula. What I want to do is:

If Cell AC15 is equal to 1 or 2 and Cell AD15 and AD16 are equal to 1, then "OK", If AC15 is equal to 3 and AD15 & 16 Equal 3 then OK, If AC15 is equal to 4 and AD15 & AD16 equal 4 then OK, If not then "ERROR"

Samples
AC15 = 1
AD15 = 1
AE15 = 1
OK

AC15 = 2
AD15 = 1
AE15 = 1
OK

These are the only two with a second condition being that AC15 can either be 1 or 2 as long as the other two are 1, not 2.

AC15 = 3
AD15 = 2
AE15 = 3
ERROR

AC15 = 1
AD15 = 3
AE15 = 1
ERROR

The remaining conditions are all three must begin with the same number.

I need some way for these situations to POP OUT at me so I know something is wrong with my coding.

Any help is greatly appreciated.

Betty


EDIT:
Another way to look at it is our financial coding has a 5 digit Index, a 5 digit PCA, and a 5 Digit Appn No. The rule is that if the Index begins with 1 or 2 the PCA must start with 1 AND the 3rd Digit of the PCA must be 1. If the Index begins with 3 or 4 then the first digit of the PCA AND the third digit of the Appn No also have to be 3. I have formulas pulling the respective digits out of the 5 digits but I'm not sure if that is the best way to start..

HELP!

Thanks,

 

[jtakw]

Hi,

Based on your description before your EDIT info, and assuming when you say AD16, you actually mean AE15:

Book1
	AC	AD	AE	AF	AG
15	1	1	1		OK
16	2	1	1		OK
17	3	2	3		ERROR
18	1	3	1		ERROR
19	3	3	3		OK
20	3	3	4		ERROR
21	4	4	4		OK
22	4	2	3		ERROR
Sheet1

Cell Formulas
Range	Formula
AG15	=IF(OR(AND(OR(AC15={1,2}),AD15=1,AE15=1),COUNTIF(AC15:AE15,{3,4})=3),"OK","ERROR")

 

[jtakw]

Hi,

Also, if you prefer to use Conditional Formatting, then use this formula for CF:

Book1
	AC	AD	AE	AF	AG
15	1	1	1		TRUE
16	2	1	1		TRUE
17	3	2	3		FALSE
18	1	3	1		FALSE
19	3	3	3		TRUE
20	3	3	4		FALSE
21	4	4	4		TRUE
22	4	2	3		FALSE
Sheet1

Cell Formulas
Range	Formula
AG15	=OR(AND(OR(AC15={1,2}),AD15=1,AE15=1),COUNTIF(AC15:AE15,{3,4})=3)

Just wrap the formula with NOT to reverse the logic.