Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
I am unable to fathom the errors I am experiencing with the following simple
problem. Please help with the necessary formula. I want to make the contents of cell C4 say pc, but only when the contents of cell B4 are anything EXCEPT stock, np, ff, or blank. That shouldnt be so difficult, but currently cell B4 has some conditional formatting which is intended to keep the cell normal if its equal to 0, or says stock or np, turns it blue if it says ff, but turns it red if it says anything else. This is achieved with the following conditions in this order: formula is =OR(B4=0,B4="stock",B4="np",C4="px in") (normal format) cell value = ff (blue) cell value not equal =stock (red) and they work fine. However, during my attempts to formulate C4, B4 has been turning red even though its empty. I appreciate that its the third condition thats turning it red, but why has OR(B4=0 ceased to invoke normal formatting if theres a formula in C4? It is OR rather than AND, after all. What formula do I need in C4, and how do I need to amend the conditional formatting on B4? Might I add that thanks to the sterling efforts of those on this group, I have always been able to find what I need to know till now. I guess Ive worked my way into a logical loophole. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
Not sure this'll help, but worth a try ...
In my experience, B4=0 and B4=empty aren't the same thing and thus wouldn't fit under Condition #1. Try adding [ B4="" ], without the [], to the OR formula in Condition #1 ... I didn't test this, but I'm guessing that it'll work br//ray |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
Is B4 also a formula that is returning null not 0?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alison KS" wrote in message ... I am unable to fathom the errors I am experiencing with the following simple problem. Please help with the necessary formula. I want to make the contents of cell C4 say 'pc', but only when the contents of cell B4 are anything EXCEPT 'stock', 'np', 'ff', or blank. That shouldn't be so difficult, but . currently cell B4 has some conditional formatting which is intended to keep the cell 'normal' if it's equal to 0, or says 'stock' or 'np', turns it blue if it says 'ff', but turns it red if it says anything else. This is achieved with the following conditions in this order: formula is =OR(B4=0,B4="stock",B4="np",C4="px in") ('normal' format) cell value = "ff" (blue) cell value not equal ="stock" (red) and they work fine. However, during my attempts to formulate C4, B4 has been turning red even though it's empty. I appreciate that it's the third condition that's turning it red, but why has OR(B4=0 ceased to invoke 'normal' formatting if there's a formula in C4? It is OR rather than AND, after all. What formula do I need in C4, and how do I need to amend the conditional formatting on B4? Might I add that thanks to the sterling efforts of those on this group, I have always been able to find what I need to know till now. I guess I've worked my way into a logical loophole. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
Formual for C4:
=IF(OR(B4<"stock",B4<"np",B4<"ff",B4<""),"pc", "") I believe the only correction you need to make to the conditional format in B4 is to the first condition. Add the following: B4="" You did not account for a null or empty cell. As for the 2nd and 3rd conditon change each to the following: #2: Formula is: =AND(B4<"",B4="ff") (blue) #3: Formula is: =AND(B4<"",B4<"stock") (red) I am in the habit of checking to see if the cell is blank as added protection. Hope that works, Les "Alison KS" wrote: I am unable to fathom the errors I am experiencing with the following simple problem. Please help with the necessary formula. I want to make the contents of cell C4 say pc, but only when the contents of cell B4 are anything EXCEPT stock, np, ff, or blank. That shouldnt be so difficult, but currently cell B4 has some conditional formatting which is intended to keep the cell normal if its equal to 0, or says stock or np, turns it blue if it says ff, but turns it red if it says anything else. This is achieved with the following conditions in this order: formula is =OR(B4=0,B4="stock",B4="np",C4="px in") (normal format) cell value = ff (blue) cell value not equal =stock (red) and they work fine. However, during my attempts to formulate C4, B4 has been turning red even though its empty. I appreciate that its the third condition thats turning it red, but why has OR(B4=0 ceased to invoke normal formatting if theres a formula in C4? It is OR rather than AND, after all. What formula do I need in C4, and how do I need to amend the conditional formatting on B4? Might I add that thanks to the sterling efforts of those on this group, I have always been able to find what I need to know till now. I guess Ive worked my way into a logical loophole. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
There's no formula in B4
"Bob Phillips" wrote: Is B4 also a formula that is returning null not 0? -- --- HTH Bob |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
I thought that Ray, but it didn't work and I'm not sure why not.
"Ray" wrote: Not sure this'll help, but worth a try ... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
Have tried this, but C4 returns 'pc' when B4 is blank. Not sure why. But B4
has stopped going red with the formula in C4. Progress. "WLMPilot" wrote: Formual for C4: =IF(OR(B4<"stock",B4<"np",B4<"ff",B4<""),"pc", "") I believe the only correction you need to make to the conditional format in B4 is to the first condition. Add the following: B4="" You did not account for a null or empty cell. As for the 2nd and 3rd conditon change each to the following: #2: Formula is: =AND(B4<"",B4="ff") (blue) #3: Formula is: =AND(B4<"",B4<"stock") (red) I am in the habit of checking to see if the cell is blank as added protection. Hope that works, Les |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
In fact, C4 returns 'pc' when B4 says 'stock', 'np' or 'ff' as well as when
it's blank. "WLMPilot" wrote: Formual for C4: =IF(OR(B4<"stock",B4<"np",B4<"ff",B4<""),"pc", "") ..... |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting quirk
So I took away the negativity and added B4=0, reversed the outcomes and that
does exactly what I want. Thanks for everyone's valuable help. "WLMPilot" wrote: Formual for C4: =IF(OR(B4<"stock",B4<"np",B4<"ff",B4<""),"pc", "") ....... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
An Interesting vba Excel quirk | Excel Discussion (Misc queries) | |||
AutoFilter Quirk | Excel Discussion (Misc queries) | |||
Formatting custom currency quirk | Excel Discussion (Misc queries) | |||
a VLOOKUP quirk | Excel Worksheet Functions |