+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting based on difference in Cells

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Conditional Formatting based on difference in Cells

    Hi, apologies if this has been posted before but my searches have not provided a solution.

    I am trying to create a conditional formatting rule that can establish the difference between 2 cells (e.g. Cell 1 = 50% / Cell 2 = 60%) so that if the difference is greater than 10% it goes red/green (if minus/plus).

    The closest I can think of would be to create a "difference" cell, however I would like the conditional formatted colour to apply to the original cells.

    Hope that made sense! Any help will be greatly appreciated as always!!

    Thanks.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting based on difference in Cells

    use the formula option in conditional formatting and put
    =ABS($A1-$B1)>0.1
    that will just satisfy if the difference is greater than 10%
    when you say -/+ i assume thats relative to say a1
    =AND(SIGN($A1-$B1)=-1,ABS($A1-$B1)>0.1) red
    =AND(SIGN($A1-$B1)=1,ABS($A1-$B1)>0.1) green
    Last edited by martindwilson; 04-26-2013 at 04:37 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting based on difference in Cells

    In Conditional Formatting rules..

    =ABS($D$1-$C$1)>10%
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    01-08-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional Formatting based on difference in Cells

    That's great, thank you both so much! I'm going to be annoying though (plus show my primitive knowledge even further) and ask how to render a 2nd format that will assign a different number if the difference is a negative percent?

    i.e. Cell 1 = 50% / Cell 2 = 30% - Currently this will be formatted to Green. If Cell 1 = 50% / Cell 2 = 70% - I need Cell 1 to now be red as Cell 1 is <10% of Cell 2.

    Thank you again!!

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting based on difference in Cells

    Try

    =d1-c1<0

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting based on difference in Cells

    didnt the 2 i posted do just that?
    =AND(SIGN($A1-$B1)=-1,ABS($A1-$B1)>0.1) red
    =AND(SIGN($A1-$B1)=1,ABS($A1-$B1)>0.1) green

  7. #7
    Registered User
    Join Date
    01-08-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Smile Re: Conditional Formatting based on difference in Cells

    Quote Originally Posted by martindwilson View Post
    didnt the 2 i posted do just that?
    =AND(SIGN($A1-$B1)=-1,ABS($A1-$B1)>0.1) red
    =AND(SIGN($A1-$B1)=1,ABS($A1-$B1)>0.1) green
    It did! But I was too slow to figure out how to use it so accurately!!

    Thank you again so much, this is exactly what I needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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