Conditional formatting

G

Gary

What's wrong with this Conditional Formula?
=AND(OR(AI16<>"No Response",AI16<>"Get
Back",AI16<>"Returning",AI16<>"Returned")),(OR(AND(AQ16="Ph",AR16>0,AP16>0,AW16="",AX16=""),(AND(AQ16="Ph",AR16>0,AP16>0,AW16="")))).

My goal is to evaluate the first OR condition and if True then evaluate
either of the AND conditions as follows: AND(OR(if true), then calculate
(OR(AND(____),AND(____))

IF any of the first OR conditions are True, then perform the calculations in
either of the AND conditions, whichever one is True. I believe my syntax is
incorrect as I get error messages when pressing OK.
 
S

Sheeloo

OR(AI16<>"No Response",AI16<>"Get Back",AI16<>"Returning",AI16<>"Returned")
will ALWAYS return TRUE

Use AND(AI16<>"No Response",AI16<>"Get
Back",AI16<>"Returning",AI16<>"Returned")
to get TRUE when AI16 does not equal any of the four statuses.
 
G

Gary

Thank you sheeloo!
What about the rest of my formula. Are the parenthesis in the correct areas?
I keep getting an error message when I click OK after entering the formula.
 
S

Sheeloo

Try
=AND(AND(AI16<>"No
Response",AI16<>"GetBack",AI16<>"Returning",AI16<>"Returned"),(OR(AND(AQ16="Ph",AR16>0,AP16>0,AW16="",AX16=""),(AND(AQ16="Ph",AR16>0,AP16>0,AW16="")))))

For it to return TRUE
1. AND(AI16<>"No
Response",AI16<>"GetBack",AI16<>"Returning",AI16<>"Returned") should be TRUE
and
Either
2. AND(AQ16="Ph",AR16>0,AP16>0,AW16="",AX16="")

or

3. AND(AQ16="Ph",AR16>0,AP16>0,AW16="")
have to be true

btw 2 is not required if you have 3 and using OR(2,3)

If this does not work then I suggest you enter the 3 AND conditions in
separate cells then combine them into the formula for clarity. Once you are
comfortable then you can combine them into one cell.
 
G

Gary

Thank you!!! Worked like a charm . Guess I still don't know enough about
formula syntax to be as creative as I'd like to be. Do you know any good
books on Excel formatting and formulas? Looked through the Dummies series and
lots of good Excel info, but not much on formulas or conditional formatting.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top