+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    11-13-2015
    Location
    Manchester, New Hampshire, USA
    MS-Off Ver
    2013
    Posts
    9

    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.
    Thanks for your help!)

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

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

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

    CF for B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-13-2015
    Location
    Manchester, New Hampshire, USA
    MS-Off Ver
    2013
    Posts
    9

    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. #4
    Registered User
    Join Date
    11-13-2015
    Location
    Manchester, New Hampshire, USA
    MS-Off Ver
    2013
    Posts
    9

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

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

    So, how would you handle that manually?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    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. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    Quote Originally Posted by ebburtis View Post
    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. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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. #9
    Registered User
    Join Date
    11-13-2015
    Location
    Manchester, New Hampshire, USA
    MS-Off Ver
    2013
    Posts
    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. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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. #11
    Registered User
    Join Date
    11-13-2015
    Location
    Manchester, New Hampshire, USA
    MS-Off Ver
    2013
    Posts
    9

    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. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    Quote Originally Posted by ebburtis View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting - The Greater of 2 Cells = Green
    By ThanksAlot in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-17-2015, 08:05 PM
  2. Replies: 5
    Last Post: 07-13-2013, 04:07 PM
  3. Conditional Formatting based on difference in Cells
    By QuietLife in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-26-2013, 06:25 AM
  4. Replies: 2
    Last Post: 02-04-2013, 02:31 PM
  5. Conditional Formatting help - based on difference between two cells
    By oscarbunny in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2012, 10:00 AM
  6. Conditional Formatting Greater than surrounding Cells
    By tmiller in forum Excel General
    Replies: 1
    Last Post: 03-30-2007, 12:15 PM
  7. Conditional formatting for the greater of 7 cells
    By guest123 in forum Excel General
    Replies: 8
    Last Post: 01-15-2007, 01:39 PM

Bookmarks

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