+ Reply to Thread
Results 1 to 2 of 2

Copy STDEV Conditional Formatting Across Multiple Ranges

  1. #1
    Registered User
    Join Date
    12-07-2022
    Location
    Charlotte, NC
    MS-Off Ver
    Excel for Office 365
    Posts
    1

    Copy STDEV Conditional Formatting Across Multiple Ranges

    See attached workbook. I have a set of conditional formatting rules applied to ROW 2. The rule is evaluating that single row and highlighting any values that are above or below 3 STDEV from the AVG. I would like to apply that same set of rules to the remaining rows such that each row is evaluated independently. For example; ROW 1 is evaluated as its own range and highlighted as such, ROW 2 is evaluated as its own range and highlighted as such, etc. Is there a way to do this efficiently?

    The only way I can figure out to do it is by selecting the currently formatted row, clicking FORMAT PAINT and then individually painting the format row by row down the sheet. When selecting the formatted row and trying to paint the formatting across the entire range at one time, the rows are not evaluated independently, but instead as a single dataset ... not what I need.

    Regarding my intent; each row in the sheet represents the monthly sales of a client and my intent is to quikcly and visually identify any outlier months for a given client.
    Attached Files Attached Files
    Last edited by bvdg03C; 12-08-2022 at 10:59 AM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,827

    Re: Copy STDEV Conditional Formatting Across Multiple Ranges

    I don't know what changes you are allowed to make. I changed your rules to be:

    1) use a formula to decide.
    2) edit formula to be something like =A2>AVERAGE($A2:$K2)+3*STDEV($A2:$K2) [for greater than 3sd above average. note the mix of relative and absolute references].
    3) edit "applies to" field so that it covers the entire table.

    That seems to work, though your sample data set does not seem to include any entries that this logic would highlight. I would suggest testing on some data sets that you know should trigger the CF and see if it is really working. (I changed to +/1 1*stdev, and added helper columns to calculate average+/-1sd for checking and it seems to work).

    Would you be allowed to use a formula for conditional formatting?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional Formatting utilizing 1 StDev Above Formula
    By jkinnick in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-21-2018, 08:05 AM
  2. Possible to apply conditional formatting across multiple ranges?
    By plasteredric in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-26-2017, 10:36 PM
  3. Conditional Formatting with Multiple Ranges
    By jkj115 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2015, 07:35 PM
  4. Replies: 10
    Last Post: 09-15-2014, 09:48 AM
  5. Replies: 15
    Last Post: 09-02-2010, 03:49 PM
  6. multiple date ranges & conditional formatting
    By gechu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2010, 09:26 AM
  7. Conditional formatting in multiple ranges.
    By SimonH.UK in forum Excel General
    Replies: 6
    Last Post: 11-22-2009, 10:26 AM

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