Results 1 to 4 of 4

Conditional format outlier 2 standard dev

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Conditional format outlier 2 standard dev

    Hello all,

    Have been stuffing around for a while trying ((obviously unsuccessfully)to get an error free piece of code that will conditionally format a column according to the following criteria . That in the event that a cell in the range is 3 standard deviations away from the mean of that range it is highlighted. I have been playing with both VBA and Formulas and can't get either to work. There are additional paremeters making life difficult. I will illustrate these by way of example. Take Range or Column A1:A90 I would like a code that would take the average for every block of 30 rows and then add and subtract 2 * Standard deviation (of the same block) from that average, if then the value in each cell falls outside that range it is highlighted. To clarify I am looking for a code that will identify outliers every block of 30 rows, each block to be treated individually i.e own average and SD. So in this short example the Average would be calculated for A1:A30 then then 2*SD for the same range would be added to and subtracted from it and then each individual cells figure that fell outside would be highlighted. this would be repeated for ranges A31:A60 etc (real data set is obviously much much larger). As i said i've messed around a bit but i'm not quite coherent yet in excel, i know a lot of words, what they mean can even form sentences though cannot have a conversation. Failed even with functions after various attempts. Thanks in advance for your help. P.S could anyone rec a good excel book that explains well how to build arguments and link things together. cheers

    Regards
    Dan
    Last edited by Cicada; 07-27-2011 at 08:59 PM.

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