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.

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

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?

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%

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

So, how would you handle that manually?

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%

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.

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.

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.

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

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%

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1