+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting percentage

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Conditional formatting percentage

    Hi, so I have a spreadsheet where I would put in a dollar value on my first column.

    My second column is a daily % change.


    So, basically what I'm trying to do is on my 2nd column, if a % change from the previous days percent change is greater than 5%, the cell turns green.

    If a % change from the previous days percent change is less than 5%, the cell turns red.
    If neither, then there is no change.


    So for example, my 2nd column will look like the following:

    0.5%
    3%
    10%
    7%
    -2%
    -5%
    1%


    So, 0.5% will be no change since its the start. 3% is no change since the change from 0.5% to 3% is 2.5% which is less than a 5% change.

    The 10% is green since the change from 3% to 10% is greater than 5%.

    The 7% is no change. The -2% is red since the change is greater than -5%.

    -5% no change. 1% is green. and so forth..

    I've tried using formula option in conditional formatting, but the outcome isn't the same. My formula is... =B2-B1>5% (then cell turns green).
    =B2-B1<5% (then cell turns red)

    Please help!

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Conditional formatting percentage

    Hello,

    if I understand this correctly, you need

    for green
    =B1-B2>5%

    for red
    =B1-B2<-5%

    If that does not work, please post a sample file.

    cheers,

  3. #3
    Registered User
    Join Date
    02-10-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional formatting percentage

    I just made an example.

    attached
    Attached Files Attached Files

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Conditional formatting percentage

    Several things:

    - the formula is D6-D7, so it should start in D7. You have started this conditional formatting in D6 instead, so all the values will be off by 1 row. Either change the formatting formula to

    =D5-D6>5%

    for green, or use your existing formula, but start one row below.

    Second, the operator for the red formula is wrong. A number needs to be less than -5%, so you need to use the < operator as per my suggestion in the previous post.

    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-10-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional formatting percentage

    ah dummy me lol
    thanks for your time and help teylyn!

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Conditional formatting percentage

    If you are satisfied with the solution(s) provided, 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

+ 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