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

BGDunbar

Board Regular
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
AE15 = 1
OK

AC15 = 2
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
AE15 = 3
ERROR

AC15 = 1
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,

Last edited by a moderator:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jtakw

Well-known Member
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">OK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">OK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style=";">ERROR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">ERROR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style=";">OK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">ERROR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">OK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style=";">ERROR</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AG15</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">AND(<font color="Green">OR(<font color="Purple">AC15={1,2}</font>),AD15=1,AE15=1</font>),COUNTIF(<font color="Green">AC15:AE15,{3,4}</font>)=3</font>),"OK","ERROR"</font>)</td></tr></tbody></table></td></tr></table><br />

Last edited:

jtakw

Well-known Member
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AG15</th><td style="text-align:left">=OR(<font color="Blue">AND(<font color="Red">OR(<font color="Green">AC15={1,2}</font>),AD15=1,AE15=1</font>),COUNTIF(<font color="Red">AC15:AE15,{3,4}</font>)=3</font>)</td></tr></tbody></table></td></tr></table><br />

Just wrap the formula with NOT to reverse the logic.

Replies
1
Views
118
Replies
5
Views
114
Replies
1
Views
43
Replies
2
Views
75
Replies
3
Views
73

1,127,632
Messages
5,625,998
Members
416,149
Latest member
Bigpotato 668

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back