+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting Based on Percent Difference

  1. #1
    Registered User
    Join Date
    06-18-2010
    Location
    ny, ny
    MS-Off Ver
    Excel 2003
    Posts
    4

    Conditional Formatting Based on Percent Difference

    I'm trying to highlight cells within a column that have a difference of 15% or greater from each other. An example would be if cells A1-A4 contained the values 90, 88, 93, and 62. I would want 62 to be highlighted because it differs by more than 15%

    any thoughts? thanks

  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 Based on Percent Difference

    Quote Originally Posted by dontcare View Post
    ...a difference of 15% or greater from each other.
    Can you be more specific about this? In your example, 62 differs from what by more than 15%?

    Once you can state that precisely then should be straightforward to define the correct formula to use in Conditional Formatting.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-18-2010
    Location
    ny, ny
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Conditional Formatting Based on Percent Difference

    62 would differ from the mean of the data (83.25) by 25% or so.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Conditional Formatting Based on Percent Difference

    CF Formula is =ABS(A1/AVERAGE(A$1:A$4) - 1)>15%
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    06-18-2010
    Location
    ny, ny
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Conditional Formatting Based on Percent Difference

    OK cool that worked for the one column i had. Now if I was going to add a bunch of other columns is there a way to apply that condition all at once?

  6. #6
    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 Based on Percent Difference

    You can highlight the cells you formatted, click on the Format Painter, then highlight the cells you still want to format. Conditional Formatting is carried along by the Format Painter just like fonts and shading. If you used a formula just like shg showed, then your column is relative and will be painted correctly into other columns.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Conditional Formatting Based on Percent Difference

    Before you do as 6SJ suggests (which is abolutely correct), look at the dollar signs in that formula. Those control how the references will change when the format is copied, just as for copying a formula. See Help about About cell and range references

  8. #8
    Registered User
    Join Date
    11-10-2009
    Location
    manila, philippines
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Conditional Formatting Based on Percent Difference

    In the CF menu where you can find all the rules you created, you can find there the range where you want to apply the CF Rule.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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