Conditional Formatting based on % difference

[gggshk]

Hello, newbie here!

Can someone help me with conditional formatting e.g.

A1 has a figure of 81%
B1 has a figure of 89%

I want A1 shaded green if it's equal to or above B1.
I also want A1 shaded yellow if it's within 2% of B1 (so either 87% or 88%)
I also want A1 shaded red if it's less than the 2% threshold (e.g. 86% or less).

Thank you!

 

[6StringJazzer]

For three colors you will need three rules. However, it is not clear what you need these rules to be.

Your specification "within 2% of B1" is the range 87-91% (not either 87% or 88%).
Also "less than the 2% threshold" seems to mean the same thing but "86% or less" doesn't seem to fit any part of your description.

 

[Joe4]

Welcome to the Board!

Use these 3 Coniditional Formatting formulas in your rules:

Green:

=A1>=B1

Yellow:

=AND(A1<B1,A1>=(B1*0.98))

Red:

=A1<(B1*0.98)

Note that in your example, 87% would actually be red, as 2% less than 89% is actually 87.22%, and 87% is less than that.
You may have to round off those formulas to 2 decimal places if you want to round off to the nearest percent and make it yellow.

 

[etaf]

you need 3 formulas in conditional formatting

I want A1 shaded green if it's equal to or above B1.
=A1 > = B1

I also want A1 shaded yellow if it's within 2% of B1 (so either 87% or 88%)
Not 2 % of the value but 2 percent absolute
=A1 > = B1 -0.02

I also want A1 shaded red if it's less than the 2% threshold (e.g. 86% or less).
=A1 < B1 -0.02

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	81%	90%
2	82%	90%			I want A1 shaded green if it's equal to or above B1. =A1 > = B1 I also want A1 shaded yellow if it's within 2% of B1 (so either 87% or 88%) Not 2 % of the value but 2 percent absolute =A1 > = B1 -0.02 I also want A1 shaded red if it's less than the 2% threshold (e.g. 86% or less). =A1 < B1 -0.02
3	83%	90%
4	84%	90%
5	85%	90%
6	86%	90%
7	87%	90%
8	88%	90%
9	89%	90%
10	90%	90%
11	91%	90%
12	92%	90%
13	93%	90%
14	94%	90%
15	95%	90%
16	96%	90%
17	97%	90%
18	98%	90%
19	99%	90%
20	100%	90%
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B20	Expression	=$A1>=$B1	text	YES
A1:B20	Expression	=$A1>=$B1-0.02	text	YES
A1:B20	Expression	=$A1<$B1-0.02	text	YES

 

[gggshk]

Sorry i don't think i've explained it as well as i could have. If we ignore the fact that these are percentages in my cells and look at it this way.

A1 has a figure of 81
B1 has a figure of 89

If A1 was to ever change to be equal to or greater than B1 it needs to be shaded green.
If A1 was to ever change to be within 2 numbers below B1 (in this instance either 87 or 88) then it needs to shade yellow.
If A1 was to ever change to be less than 2 numbers below B1 (in this instance 86 or less) then it needs to shade red.

 

[Joe4]

Ah, I see a little difference in interpretation of your original question.

When you said 2%, I assumed you meant within 2% of the value on B1 (so a percent of a percent).
etaf interpretted as a hard-coded 2% less than the value in B1.

For example, in my interpretation, if 50% was in cell B1, 2% of 50% is 1%, so it would be looking for 49%
However, if you want to subtract a straight 2%, it would be 48%

Based on your original example, I am guessing that etaf's interpretation (and solution) may be the one you are looking for.

 

[etaf]

as per my example
and formulas