Conditional Formatting

[Guest]

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%.

 

[Bob Phillips]

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)

 

[Bob Phillips]

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 Format>Conditional 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)

 

[Dave O]

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.