|
CF with 4 conditions
I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
If you use "Formula is", rather than "Cell value is", then you can use an
OR() condition. -- David Biddulph "sahafi" wrote in message ... I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
While your formula worked with the red formatting, the other 2 formatting are
not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
Your conditional formatting works (I recreated it using the first versions of
#2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
Thanks for the reply. But those are not my conditions.
Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
Try these for your formulas:
Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
While the 'Red' and 'Green' formatting are executed correctly, the yellow
format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
There is no overlap among the formulas, so the order shouldn't matter. As
far as I can tell, they work they way they're supposed to. Unless I'm misunderstanding your criteria. When I try it, I get the following results: When B4 contains the value 100, I input the following values into J7 with the following results: 99.94 = RED 99.95 = GREEN 100.00 = GREEN 100.01 = YELLOW 100.05 = YELLOW 100.06 = RED Is that not right? HTH, Elkar "sahafi" wrote: While the 'Red' and 'Green' formatting are executed correctly, the yellow format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
My numbers formatted as %.
Using your example, if B4 = 100% then the values on my range will be formatted as follow: Green <=100% and =95% Yellow 100% and <=105% Red 105% || Red <95% and 0.001% If <=0.001 (zero) then do not format. Hope it's clear now. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: There is no overlap among the formulas, so the order shouldn't matter. As far as I can tell, they work they way they're supposed to. Unless I'm misunderstanding your criteria. When I try it, I get the following results: When B4 contains the value 100, I input the following values into J7 with the following results: 99.94 = RED 99.95 = GREEN 100.00 = GREEN 100.01 = YELLOW 100.05 = YELLOW 100.06 = RED Is that not right? HTH, Elkar "sahafi" wrote: While the 'Red' and 'Green' formatting are executed correctly, the yellow format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
It still works for me, formatted as percent or not. As long as both cells
are formatted the same way. If, as you mentioned, the Green and Red conditions are working, and only the Yellow is wrong, then take a closer look at the formulas. Make sure you typed them exactly (or better yet, copy and paste from this post), and didn't leave anything out. If still not working, then what is happening when the condition should be yellow? Is red or green filling in? Or is it blank? What happens if you delete the Red and Green conditions, leaving only the Yellow? HTH, Elkar "sahafi" wrote: My numbers formatted as %. Using your example, if B4 = 100% then the values on my range will be formatted as follow: Green <=100% and =95% Yellow 100% and <=105% Red 105% || Red <95% and 0.001% If <=0.001 (zero) then do not format. Hope it's clear now. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: There is no overlap among the formulas, so the order shouldn't matter. As far as I can tell, they work they way they're supposed to. Unless I'm misunderstanding your criteria. When I try it, I get the following results: When B4 contains the value 100, I input the following values into J7 with the following results: 99.94 = RED 99.95 = GREEN 100.00 = GREEN 100.01 = YELLOW 100.05 = YELLOW 100.06 = RED Is that not right? HTH, Elkar "sahafi" wrote: While the 'Red' and 'Green' formatting are executed correctly, the yellow format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
I have copied the formulas you have provided, and still no yellow formatting.
The values that are supposed to be highlighted yellow, they get formatted as green as well as the ones supposed to be green. I have removed the 'Green' condition, but still no yellow. Then I deleted both conditions (red and green), and still no yellow!!. I don't know why it's working for you but not for me. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: It still works for me, formatted as percent or not. As long as both cells are formatted the same way. If, as you mentioned, the Green and Red conditions are working, and only the Yellow is wrong, then take a closer look at the formulas. Make sure you typed them exactly (or better yet, copy and paste from this post), and didn't leave anything out. If still not working, then what is happening when the condition should be yellow? Is red or green filling in? Or is it blank? What happens if you delete the Red and Green conditions, leaving only the Yellow? HTH, Elkar "sahafi" wrote: My numbers formatted as %. Using your example, if B4 = 100% then the values on my range will be formatted as follow: Green <=100% and =95% Yellow 100% and <=105% Red 105% || Red <95% and 0.001% If <=0.001 (zero) then do not format. Hope it's clear now. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: There is no overlap among the formulas, so the order shouldn't matter. As far as I can tell, they work they way they're supposed to. Unless I'm misunderstanding your criteria. When I try it, I get the following results: When B4 contains the value 100, I input the following values into J7 with the following results: 99.94 = RED 99.95 = GREEN 100.00 = GREEN 100.01 = YELLOW 100.05 = YELLOW 100.06 = RED Is that not right? HTH, Elkar "sahafi" wrote: While the 'Red' and 'Green' formatting are executed correctly, the yellow format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
Hmm.... I'm running out of ideas here. Is the data in B4 or J7 the result of
a formula? If so, perhaps try manually typing data over these formulas just to see what happens? Sometimes, due to cell formatting, the value returned by a formula, and the value displayed in the cell aren't the same. The conditional formatting bases its results off of the actual value of a cell, not the displayed value of a cell. The only other thing I can think of is maybe an issue with your color settings. Try changing the Yellow color to Blue or something else and see what happens. HTH, Elkar "sahafi" wrote: I have copied the formulas you have provided, and still no yellow formatting. The values that are supposed to be highlighted yellow, they get formatted as green as well as the ones supposed to be green. I have removed the 'Green' condition, but still no yellow. Then I deleted both conditions (red and green), and still no yellow!!. I don't know why it's working for you but not for me. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: It still works for me, formatted as percent or not. As long as both cells are formatted the same way. If, as you mentioned, the Green and Red conditions are working, and only the Yellow is wrong, then take a closer look at the formulas. Make sure you typed them exactly (or better yet, copy and paste from this post), and didn't leave anything out. If still not working, then what is happening when the condition should be yellow? Is red or green filling in? Or is it blank? What happens if you delete the Red and Green conditions, leaving only the Yellow? HTH, Elkar "sahafi" wrote: My numbers formatted as %. Using your example, if B4 = 100% then the values on my range will be formatted as follow: Green <=100% and =95% Yellow 100% and <=105% Red 105% || Red <95% and 0.001% If <=0.001 (zero) then do not format. Hope it's clear now. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: There is no overlap among the formulas, so the order shouldn't matter. As far as I can tell, they work they way they're supposed to. Unless I'm misunderstanding your criteria. When I try it, I get the following results: When B4 contains the value 100, I input the following values into J7 with the following results: 99.94 = RED 99.95 = GREEN 100.00 = GREEN 100.01 = YELLOW 100.05 = YELLOW 100.06 = RED Is that not right? HTH, Elkar "sahafi" wrote: While the 'Red' and 'Green' formatting are executed correctly, the yellow format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
CF with 4 conditions
OK, it's working now. It turn out that my lookup cell (B4) was of a text data
type instead of percentage! I don't know why I didn't check that. It's my fault. But thank you for being patient, and thaks for the help. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Hmm.... I'm running out of ideas here. Is the data in B4 or J7 the result of a formula? If so, perhaps try manually typing data over these formulas just to see what happens? Sometimes, due to cell formatting, the value returned by a formula, and the value displayed in the cell aren't the same. The conditional formatting bases its results off of the actual value of a cell, not the displayed value of a cell. The only other thing I can think of is maybe an issue with your color settings. Try changing the Yellow color to Blue or something else and see what happens. HTH, Elkar "sahafi" wrote: I have copied the formulas you have provided, and still no yellow formatting. The values that are supposed to be highlighted yellow, they get formatted as green as well as the ones supposed to be green. I have removed the 'Green' condition, but still no yellow. Then I deleted both conditions (red and green), and still no yellow!!. I don't know why it's working for you but not for me. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: It still works for me, formatted as percent or not. As long as both cells are formatted the same way. If, as you mentioned, the Green and Red conditions are working, and only the Yellow is wrong, then take a closer look at the formulas. Make sure you typed them exactly (or better yet, copy and paste from this post), and didn't leave anything out. If still not working, then what is happening when the condition should be yellow? Is red or green filling in? Or is it blank? What happens if you delete the Red and Green conditions, leaving only the Yellow? HTH, Elkar "sahafi" wrote: My numbers formatted as %. Using your example, if B4 = 100% then the values on my range will be formatted as follow: Green <=100% and =95% Yellow 100% and <=105% Red 105% || Red <95% and 0.001% If <=0.001 (zero) then do not format. Hope it's clear now. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: There is no overlap among the formulas, so the order shouldn't matter. As far as I can tell, they work they way they're supposed to. Unless I'm misunderstanding your criteria. When I try it, I get the following results: When B4 contains the value 100, I input the following values into J7 with the following results: 99.94 = RED 99.95 = GREEN 100.00 = GREEN 100.01 = YELLOW 100.05 = YELLOW 100.06 = RED Is that not right? HTH, Elkar "sahafi" wrote: While the 'Red' and 'Green' formatting are executed correctly, the yellow format has been ignored. The logic looks OK, but it seems to me the issue is how to order them correctly. I have tried that, but didn't work for me. May be someone could take a look at the formulas and see what's missing. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Try these for your formulas: Red: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) Yellow: =AND(J7$B$4,J7<=$B$4+0.05) Green: =AND(J7<=$B$4,J7=$B$4-0.05) HTH, Elkar "sahafi" wrote: Thanks for the reply. But those are not my conditions. Basically, if $B$4 is 106% I would like my conditions to be formatted as: When it's equal to B4 and between B4 and B4 minus 5% then show 'green'. When it's greater than B4 but less than B4 plus 5% then show 'Yellow'. When it's greater than (B4+5%) show 'Red' When it's less than (B4-5%), but greater than zero also show 'Red'. If it's zero do not format (no background fill). I hope I made it clearer this time. Thanks. -- If u change the way u look @ things, the things u look at change. "Tom Hutchins" wrote: Your conditional formatting works (I recreated it using the first versions of #2 and #3 below). J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than 0.001 J7 is also yellow if it is zero and B4 is between zero and -.05, not inclusive. Hope this helps, Hutch "sahafi" wrote: While your formula worked with the red formatting, the other 2 formatting are not working as I would like them to. Here's all the formulas: B4 is the value i'm basing my conditions on. J7 is the beginning of my range. #1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working. #2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'. #3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'. I'm only getting 2 format out of it. Either red and green, or red and yellow. I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it didn't work either. #2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001) #3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001) I'm not sure if it's the order of the conditions, or I missed something. Thanks. -- If u change the way u look @ things, the things u look at change. "Elkar" wrote: Its a common misconception that Conditional Formatting is limited to 3 conditions. Actually, it is limited to 3 formats (4 if you count the default), not conditions. With the use of AND and OR functions, you can have virtually unlimited conditions. For your third condition (red), try something like this: =OR(B1A1+.05,AND(B1<A1-.05,B1.001)) HTH, Elkar "sahafi" wrote: I need to have a conditianal formatting applied to my data as follow: I would like to compare the condition to a value that changes, say every month. On $A$1 I have my value 106.2%. Condition1 when the value is between A1 and A1 + 0.05 = Yellow Condition2 when the value is between A1 and A1 - 0.05 = Green Condition3 when the value is = (A1 + 0.05) = Red Condition4 when the value is between (A1 - 0.05) and 0.001 = Red Otherwise no format (< 0.0001). Leave zero values without formatting. I managed to get the first 3 conditions to work but not the last one as Excel will allow only 3 condition plus the blank one. I have tried to add additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I ended up with the same result. I have also tried the CFPlus Add in, but it didn't work. Any help is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
| All times are GMT +1. The time now is 07:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com