Coditional Format
Guys, I'm a little unsure how to complete this one. In the example below I
am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than 80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1 yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I can get using the Formula Is box in the conditional format function ,, but how do I get the "between" to work ?? A B C 1 13% 19% 68% |
Coditional Format
There is a hierarchy with Conditional Formatting - the first condition
takes precedence. So, make your first formula: =C190% (set to green) Second condition: =C180% (set to yellow) Third condition: =AND(C1<80%,C1<"") (set to red) The last condition avoids the colour if the cell is empty. You could use AND to specify a range of values, but by setting the conditions in this order you can avoid that. Hope this helps. Pete On Jul 23, 11:23*am, John Moore wrote: Guys, I'm a *little unsure how to complete this one. In the example below I am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than 80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1 yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I can get using the Formula Is box in the conditional format function ,, but how do I get the "between" to work ?? * * * * A * * * * B * * * *C 1 * * 13% * *19% * 68% |
Coditional Format
Thanks Pete ,, works well .... saves me some time ,, appreciate it
"Pete_UK" wrote: There is a hierarchy with Conditional Formatting - the first condition takes precedence. So, make your first formula: =C190% (set to green) Second condition: =C180% (set to yellow) Third condition: =AND(C1<80%,C1<"") (set to red) The last condition avoids the colour if the cell is empty. You could use AND to specify a range of values, but by setting the conditions in this order you can avoid that. Hope this helps. Pete On Jul 23, 11:23 am, John Moore wrote: Guys, I'm a little unsure how to complete this one. In the example below I am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than 80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1 yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I can get using the Formula Is box in the conditional format function ,, but how do I get the "between" to work ?? A B C 1 13% 19% 68% |
Coditional Format
Hey John,
There is a problem in your description, what if C1 is exactly 80% or 90%? As described they would return no color. Assuming you would like the crossovers to start at 80% and 90%, also assuming your cell C1 is formatted as percentage, I believe your Conditional Formats could be: 1st condition: =$C$1<0.8 2nd condition: =AND($C$10.79,$C$1<0.9) 3rd condition: =$C$10.89 Or to simplify, you could try: 1st condition: =$C$1<0.8 2nd condition: =$C$1<0.9 3rd condition: =$C$10.89 Since the first condition eliminates the need to restrict the lower portion of the 2nd condition, the AND($C$10.79,_____) is not needed. They both work for me, they should work for you. HTH -Minitman On Wed, 23 Jul 2008 03:23:03 -0700, John Moore wrote: Guys, I'm a little unsure how to complete this one. In the example below I am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than 80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1 yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I can get using the Formula Is box in the conditional format function ,, but how do I get the "between" to work ?? A B C 1 13% 19% 68% |
Coditional Format
You're welcome, John - thanks for feeding back.
Pete On Jul 23, 12:08*pm, John Moore wrote: Thanks Pete ,, works well .... saves me some time ,, appreciate it |
All times are GMT +1. The time now is 11:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com