| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting
What's wrong with this Conditional Formula?
=AND(OR(AI16<"No Response",AI16<"Get Back",AI16<"Returning",AI16<"Returned")),(OR(AND (AQ16="Ph",AR160,AP160,AW16="",AX16=""),(AND(AQ1 6="Ph",AR160,AP160,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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting
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. "Gary" wrote: What's wrong with this Conditional Formula? =AND(OR(AI16<"No Response",AI16<"Get Back",AI16<"Returning",AI16<"Returned")),(OR(AND (AQ16="Ph",AR160,AP160,AW16="",AX16=""),(AND(AQ1 6="Ph",AR160,AP160,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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting
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. "Sheeloo" wrote: 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. "Gary" wrote: What's wrong with this Conditional Formula? =AND(OR(AI16<"No Response",AI16<"Get Back",AI16<"Returning",AI16<"Returned")),(OR(AND (AQ16="Ph",AR160,AP160,AW16="",AX16=""),(AND(AQ1 6="Ph",AR160,AP160,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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting
Try
=AND(AND(AI16<"No Response",AI16<"GetBack",AI16<"Returning",AI16< "Returned"),(OR(AND(AQ16="Ph",AR160,AP160,AW16=" ",AX16=""),(AND(AQ16="Ph",AR160,AP160,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",AR160,AP160,AW16="",AX16="") or 3. AND(AQ16="Ph",AR160,AP160,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. "Gary" wrote: 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. "Sheeloo" wrote: 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. "Gary" wrote: What's wrong with this Conditional Formula? =AND(OR(AI16<"No Response",AI16<"Get Back",AI16<"Returning",AI16<"Returned")),(OR(AND (AQ16="Ph",AR160,AP160,AW16="",AX16=""),(AND(AQ1 6="Ph",AR160,AP160,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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting
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. "Sheeloo" wrote: Try =AND(AND(AI16<"No Response",AI16<"GetBack",AI16<"Returning",AI16< "Returned"),(OR(AND(AQ16="Ph",AR160,AP160,AW16=" ",AX16=""),(AND(AQ16="Ph",AR160,AP160,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",AR160,AP160,AW16="",AX16="") or 3. AND(AQ16="Ph",AR160,AP160,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. "Gary" wrote: 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. "Sheeloo" wrote: 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. "Gary" wrote: What's wrong with this Conditional Formula? =AND(OR(AI16<"No Response",AI16<"Get Back",AI16<"Returning",AI16<"Returned")),(OR(AND (AQ16="Ph",AR160,AP160,AW16="",AX16=""),(AND(AQ1 6="Ph",AR160,AP160,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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting
You are welcome. Just try to enter one formula at a time...
Also get into the habit of looking up HELP... Try "Learn Excel 97-2003 from Mr Excel" You can download a trial copy from http://www.mrexcel.com/learnexcel2.shtml "Gary" wrote: 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. "Sheeloo" wrote: Try =AND(AND(AI16<"No Response",AI16<"GetBack",AI16<"Returning",AI16< "Returned"),(OR(AND(AQ16="Ph",AR160,AP160,AW16=" ",AX16=""),(AND(AQ16="Ph",AR160,AP160,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",AR160,AP160,AW16="",AX16="") or 3. AND(AQ16="Ph",AR160,AP160,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. "Gary" wrote: 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. "Sheeloo" wrote: 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. "Gary" wrote: What's wrong with this Conditional Formula? =AND(OR(AI16<"No Response",AI16<"Get Back",AI16<"Returning",AI16<"Returned")),(OR(AND (AQ16="Ph",AR160,AP160,AW16="",AX16=""),(AND(AQ1 6="Ph",AR160,AP160,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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
