+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting - comparing value to previous cell in column

  1. #1
    Registered User
    Join Date
    11-05-2010
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2007
    Posts
    2

    Conditional Formatting - comparing value to previous cell in column

    Hi all,

    I wonder if someone can help........

    I am trying to apply conditional formatting to cells in a column. I want to highlight any cells which show a greater than 10% drop from the previous value, in the cell above.

    For example: if D3 is less than 0.9 times the value of D2, then highlight the cell in red. Similarly highlight the cell when D4 is less than 0.9 times the value of D3, D5 is less than 0.9 times the value of D4, etc, etc. I have attached a section of my spreadsheet to demonstrate.

    I can set the formatting for the first cell with {cell value<"0.9*$D$2"} and this highlights the cell as required.

    However, when I apply the formatting to $D$3:$D$14 (in the conditional formatting rules manager) it compares each cell value to the value in D2, rather than to the cell above. So I tried deleting the dollar signs to give {cell value<"0.9*D2"}. This still compared all cells to D2. I tried using {cell value<"0.9*R[-1]C"}, but this returned an invalid formula error.

    At wits end, any suggestions gratefully received.
    Thanks
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional Formatting - comparing value to previous cell in column

    You were on the right track with removing the dollar signs. Not sure where you went wrong after that, see attached.

    Edit: I will that I have found conditional formatting in 2007 to be more powerful but a little quirky. I have found similar problems trying to apply a formula-based condition to multiple cells. It's confusing when you look at a single condition that is indicated to apply to a range of cells, but the formula refers to a specific cell and that cell is not the one that is currently selected. How to figure out what formula applies to the cell I am in right now? What I typically do to sidestep the confusion is set the formatting up for one cell, with no dollar signs, then use Format Painter to apply it to all the other affected cells.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 11-05-2010 at 08:34 AM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-05-2010
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Conditional Formatting - comparing value to previous cell in column

    You little beauty, thank-you!

    (now very relieved, but very exasperated by excel's eccentricities!!)

  4. #4
    Registered User
    Join Date
    04-06-2011
    Location
    Nowhere, Specific
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Conditional Formatting - comparing value to previous cell in column

    Quote Originally Posted by 6StringJazzer View Post
    then use Format Painter to apply it to all the other affected cells.

    Thank you for the "Format Painter" solution. I had the same issues when trying to track follow-ups. For instance, if an individual needed to return to our office a year from their original visit I put that date in Column K. I recorded the actual date of their proposed appointment in Column L and wanted this to be formatted differently if that visit fell outside of two-months from the date in Column K. Even after removing the absolute references from the formula I still could not get it to work properly until I setup the rule in the first cell and used the format painter to apply it to the other cells in the column as you suggested.

    Thank you!

+ 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