Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Formatting
I have a question about conditional formatting. How can I use one condition
to say "make the cell yellow if the number being judged is 15-20 percent below or 15-20 percent above the target"? I ask how can i use one because there are two other conditions +- 15%. and +- 20%. |
#2
|
|||
|
|||
depending upon the target, and where the cells are. Assume the target is
100, and the -- HTH RP (remove nothere from the email address if mailing direct) "KCi" wrote in message ... I have a question about conditional formatting. How can I use one condition to say "make the cell yellow if the number being judged is 15-20 percent below or 15-20 percent above the target"? I ask how can i use one because there are two other conditions +- 15%. and +- 20%. |
#3
|
|||
|
|||
Try again...
depending upon the target, and where the cells are. Assume the target is 100, and the target cells are A1:A100 Select A1:A100 Menu FormatConditional Formatting Change condition to Formula Is Add formula of =AND(A1=100-(100*15%),A1<=100+(100*15%)) Click format, select Pattern and choose a colour OK Add condition repeat with a formula of =AND(A1=100-(100*20%),A1<=100+(100*20%)) OK out -- HTH RP (remove nothere from the email address if mailing direct) "KCi" wrote in message ... I have a question about conditional formatting. How can I use one condition to say "make the cell yellow if the number being judged is 15-20 percent below or 15-20 percent above the target"? I ask how can i use one because there are two other conditions +- 15%. and +- 20%. |
#4
|
|||
|
|||
I got this to work with the cell to be tested as A2 and the target in
B2. I entered 75 in A2 and 80 as target in B2. With the cell pointer on A2, click Format Conditional Formatting. Set the first condition to "Formula Is" in the drop down, and then enter =ABS(1-(A2/B2))<=0.15. This checks to see if the test cell is within +-15% of the target. (I set the format to paint the cell yellow). Then click the Add button and set condtion 2 to "Formula is" and enter =AND(ABS(1-(A2/B2))0.15,ABS(1-(A2/B2))<=0.2) .... which checks to see if the target is greater than +/-15% but less than +/-20%. I set the format to paint the cell blue under these conditions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Losing conditional formatting when saving | Excel Discussion (Misc queries) | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) |