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

Hello Dan, select A1:A90 (or a longer range) and use this formula in conditional formatting

=ABS(A1-AVERAGE(OFFSET(A$1:A$30,FLOOR(ROWS(A$1:A1)-1,30),0)))>STDEV(OFFSET(A$1:A$30,FLOOR(ROWS(A$1:A1)-1,30),0))*2