Closed Thread
Results 1 to 10 of 10

Conditional formatting based on a percentage difference

  1. #1
    Forum Contributor
    Join Date
    02-14-2008
    Location
    Manchester, England
    MS-Off Ver
    2007
    Posts
    110

    Conditional formatting based on a percentage difference

    Hello

    I wrote a question a few days ago but might not have been as clear as I could be. Apologies.

    Using conditional formatting i am trying to write a formula that changes colour on the percentage difference of another cell.

    An example would be if cell A1 contained a target of 5% in cell A2 would be an actual. If cell A2 is equal to A1 or better then it would be green. If cell A2 was within 10% less of A1 (i.e 4.5%-4.99%) then amber and red for the target minus 10% (i.e <4.49%)

    I want to do this as a conditional formatting formula as the target figure will change but 10% difference will remain the same.

    Hope someone can help

    Thanks
    Last edited by Dan27; 04-03-2009 at 10:15 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting based on a percentage difference

    To reduce confusion.... can you give 3 examples of what would turn the cell green, yellow, red.

    I.e What would be in A1 and what would be in A2 for each.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    02-14-2008
    Location
    Manchester, England
    MS-Off Ver
    2007
    Posts
    110

    Re: Conditional formatting based on a percentage difference

    If Cell A1 Contained 100

    A2 100 or above green
    between 90 and 100 amber
    below 90 red

    If cell A1 contained 5

    A2 5 or above would be green
    between 4.5 and 4.99 amber
    below 4.5 red

    Hope this helps.

    Thanks

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting based on a percentage difference

    Invoke Conditional Formatting on Cell A2 and apply conditions as attached...
    Attached Images Attached Images

  5. #5
    Forum Contributor
    Join Date
    02-14-2008
    Location
    Manchester, England
    MS-Off Ver
    2007
    Posts
    110

    Re: Conditional formatting based on a percentage difference

    That works great thanks.

    I've added to your reputation.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting based on a percentage difference

    Thank you

    Can you also please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  7. #7
    Registered User
    Join Date
    04-05-2010
    Location
    OKC, OK
    MS-Off Ver
    Excel 2007
    Posts
    2

    Conditional formatting based on a percentage difference

    I realize this was asked previously but the answer was for a previous version of Excel. I am working in Excel 2007.

    Using conditional formatting I want a cell to change color based on the percentage difference of another cell.

    An example would be if cell A1 contained a actual of 4.4, in cell A2 would be a goal of 4.5. If cell A3 is within 2% (above or below) - cell turns green. If it's within 10% (above or below) - yellow. If it's more han 10% - red.

    Thanks for any help you can provide.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting based on a percentage difference

    Sorry, your post still does not conform...

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

  9. #9
    Registered User
    Join Date
    11-13-2010
    Location
    Austin, tx
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Conditional formatting based on a percentage difference

    I am trying to get conditional formatting to work with a stop sign changing depending on where the actual percent is compared to the target. For example see example below:

    Status Metric Target Actual
    ICON ABA < 5% 4.7%

    I have a table set up such as the one above and I want the stop sign icon to change depending on what difference between the target 5% and the actual percent. If the actual is within 5% of the target then the stop sign will be yellow and if it is above 5% of the target it will turn red and if it is below the 5% TARGET then it would be green.

    Does anyone know how to make this work? I can make it work with numbers, but percentages will not work.

    Thank you!

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Conditional formatting based on a percentage difference

    @tderoma1, welcome to the board but please note:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread.
    If you feel it's particularly relevant, provide a link to the other thread.

Closed 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