# Conditional Formatting when difference between 2 cells is greater than 5%

1. ## Conditional Formatting when difference between 2 cells is greater than 5%

I need to be able to use conditional format to do the following:

Example:
A2 is 90%
B2 is 70%
C2 is 96%

B2 is more than 5% BELOW A2 so the cell turns red
C2 is more than 5% ABOVE A2, so the cell turns green

For some reason (maybe because it is after 6:00 on Friday night) I can't get my brain around this.  Register To Reply

2. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

Use the "Use a Formula..." option with CF.

CF for B2:
Formula:  `Please Login or Register  to view this content.`

CF for B2:
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

Wonderful - this works. But when I try to copy it to the whole sheet I'm having issues. I've tried \$ signs before the A2 and that works when copying across the row, but not the rest of the sheet. Without the \$ I thought I could copy it down the column, and I thought it worked but I ended up with a cell that got highlighted that was only 2% and not the 5% required by the formula.

Am I going to need to do a new formula for each row and then copy it across?  Register To Reply

4. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

I think I figured something out. The formulas work mathematically until the numbers get smaller. So when the numbers are

A2=39%
B2=41%

39% * 105% = 41% but 41% is not more than 5% higher than 39%  Register To Reply

5. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

So, how would you handle that manually?  Register To Reply

6. ## Re: Conditional Formatting when difference between 2 cells is greater than 5% CF for B2: =B2<A2-5%

CF for C2: =C2>A2+5%  Register To Reply

7. ## Re: Conditional Formatting when difference between 2 cells is greater than 5% Originally Posted by ebburtis I think I figured something out. The formulas work mathematically until the numbers get smaller. So when the numbers are

A2=39%
B2=41%

39% * 105% = 41% but 41% is not more than 5% higher than 39%
Hi,

But 39% * 105% = 40.95%. So 41% IS higher.   Register To Reply

8. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

...further to post #7.

Maybe you are wanting to round up numbers but unless you tell us we just don't know.  Register To Reply

9. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

It is higher, but not more than 5% more which is what I wanted the formatting to test for. This is the formula that ended up working:

=B2>A2+5% and =B2<A2-5%. I finally figured it out on my own but @Phuocam posted it here before I checked back.  Register To Reply

10. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

Just for clarity

B2<A2-5% is the same as B2<A2*95% - the formula in post #2.

in other words a number less 5% of that number is the same as the number times 95%.  Register To Reply

11. ## Re: Conditional Formatting when difference between 2 cells is greater than 5%

Yes, but what I want is the number that is 5 percentage points above or below the original percent - not 5% of the number. So it's not really the same. It may have been in the wording of my original request. If so, I apologize.

If we use the same 39%

39% * 105% = 40.95%
but
39% + 5% = 44%  Register To Reply

12. ## Re: Conditional Formatting when difference between 2 cells is greater than 5% Originally Posted by ebburtis Yes, but what I want is the number that is 5 percentage points above or below the original percent - not 5% of the number. So it's not really the same. It may have been in the wording of my original request. If so, I apologize.

If we use the same 39%

39% * 105% = 40.95%
but
39% + 5% = 44%
Indeed I agree and if it is indeed absolute percentage 'points' you're requiring that's correct.
Normally when measuring stuff against a target the percentage 'difference' to the target is a more traditional statistical measure.  Register To Reply