+ 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 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
    26,957

    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

    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 2000/3/7/10/13/16
    Posts
    50,082

    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
    2010
    Posts
    4,460

    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 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
    26,957

    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 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
    26,957

    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 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
    26,957

    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 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
    26,957

    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