+ Reply to Thread
Results 1 to 11 of 11

INDIRECT problem for anomaly detection with conditional formatting

  1. #1
    Registered User
    Join Date
    09-10-2014
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    15

    INDIRECT problem for anomaly detection with conditional formatting

    Hello everybody,

    I have values in columns and would like to use conditional formatting to mark anomalies in the data.

    The following formula provides good results:

    =ABS(A5-AVERAGE(A5:A56)>STDEV.P(A5:A56)*2

    But I have to use INDIRECT for conditional formatting with a formula (true/false), so in a first step I did this:

    =ABS(INDIRECT(ADDRESS(ROW(); COLUMN()))-AVERAGE(A5:A56)>STDEV.P(A5:A56)*2

    But how can I get the values of the rows 5 to 56 INDIRECTly?

    Thanks in advance!
    eyestorm

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDIRECT problem for anomaly detection with conditional formatting

    The first formula is already a TRUE/FALSE construct if you enclose the calcs properly, isn't it?

    =(ABS(A5-AVERAGE(A5:A56))>(STDEV.P(A5:A56)*2)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-10-2014
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    15

    Re: INDIRECT problem for anomaly detection with conditional formatting

    Yes, it is. And I have to transform it from static (specific cells) to relative (row 5-56 in same column for AVERAGE and STDEV) so that I don't need to configure hundreds of rules for conditional formatting.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDIRECT problem for anomaly detection with conditional formatting

    So, when this CF formula is applied to A5, it should read:
    =(ABS(A5-AVERAGE(A5:A56))>(STDEV.P(A5:A56)*2)

    When you apply that formula to A6, what would the formula be?

    Or B5?

  5. #5
    Registered User
    Join Date
    09-10-2014
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    15

    Re: INDIRECT problem for anomaly detection with conditional formatting

    Yes, the rule would be like this: =(ABS(A6-AVERAGE(A5:A56))>(STDEV.P(A5:A56)*2)

    But I have a lot of columns and want to apply one rule to all cells. Therefore I try to make it generic with INDIRECT.

    Excel-conditional-formatting-031.png

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDIRECT problem for anomaly detection with conditional formatting

    So far I have no belief that INDIRECT is needed. Did you see my specific questions in post #4?

  7. #7
    Registered User
    Join Date
    09-10-2014
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    15

    Re: INDIRECT problem for anomaly detection with conditional formatting

    Hi PMJBeaucaire, I answered your question in #5, it would be A6.

    Does Excel automatically interpret CF formulas relatively so that they are adapted for the area the rule is applied to?

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: INDIRECT problem for anomaly detection with conditional formatting

    If the formula adjusts as such

    A5: =(ABS(A5-AVERAGE(A5:A56))>(STDEV.P(A5:A56)*2)
    A6: =(ABS(A6-AVERAGE(A5:A56))>(STDEV.P(A5:A56)*2)
    B5: =(ABS(B5-AVERAGE(B5:B56))>(STDEV.P(B5:B56)*2)
    B6: =(ABS(B6-AVERAGE(B5:B56))>(STDEV.P(B5:B56)*2)

    Then the formula should be
    =(ABS(A5-AVERAGE(A$5:A$56))>(STDEV.P(A$5:A$56)*2)

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDIRECT problem for anomaly detection with conditional formatting

    Thanks Jonmo1, Yep. As you can see no INDIRECT() is needed. You can LOCK the A5:A56 rows by using the $ sign anchors make them absolute. Any part of a cell reference without a $ is relative and will adjust from cell to cell across rows and columns.

  10. #10
    Registered User
    Join Date
    09-10-2014
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    15

    Re: INDIRECT problem for anomaly detection with conditional formatting

    Thank you, JBeaucaire and Jonmo1, it works! My approach was way too complicated and not needed as I know now.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDIRECT problem for anomaly detection with conditional formatting

    As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
    FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

+ 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. [SOLVED] Conditional Formatting INDIRECT between dates
    By Will_iam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2012, 10:46 AM
  2. [SOLVED] Combining INDIRECT with OR for use in conditional formatting
    By ScotyB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2012, 01:17 AM
  3. Conditional Formatting and Indirect Function
    By gmc2k2 in forum Excel General
    Replies: 4
    Last Post: 02-03-2011, 09:30 PM
  4. Resolved >>> Conditional formatting with INDIRECT and AND
    By mike_something in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2007, 07:54 AM
  5. Formatting Anomaly
    By Christopher Weaver in forum Excel General
    Replies: 2
    Last Post: 05-13-2005, 06:06 PM

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