Conditional nested formatting for cell color changes

[Gary]

I don't completely understand the rules when combining "AND" and "OR"
together in various ways to test various conditions to turn a cell red or
green depending upon the results of the "AND(OR" or "OR(AND" or multiple
nested AND(OR tests. I think I must have my ( ) in the wrong places. How do I
tell Excel which tests I want in the first nested "AND(OR" statement, and
separate the first test from the 2nd "AND(OR", etc. Does it require (( ...)),
ie double parenthesis is certain places to group each test separately? Where
can I go to find some reading material on this?

 

[ShaneDevenshire]

Hi,

Let's use ((A+B)/(C-D) as an example.

Excel always evaluates from the inner parentheses first, and then from left
to right. Then it works with multiplication and division, and then addition
and subtraction. So in the above example the first things that are done are
A+B and then C-D then the division.

Now to AND and OR

OR(AND(A1>1,B1="Shane"),AND(C1="Accounting",D1<=TODAY()))

The inner parenthesis are evaluated first, it does not matter whether the
words are AND or OR. So the left hand AND would return TRUE or FALSE, and
the right hand AND would return TRUE or FALSE. Then the OR is evaluated, it
might be

OR(TRUE,FALSE) answer TRUE - one or the other is true then OR is true
OR(FALSE,FALSE) answer FALSE - neither is true so OR is false
there are other combinations.

If this helps, please click the Yes button.