How to find a data anomaly in a row with a running average?

[Ed Harris]

I have moved from three data points per row to 30 or 80 and my previous solution is no longer good for finding anomalies in the data rows. Because the data often follows a slope looking for a difference from the mean is no longer sufficient. I think I need to look for a difference from a running mean; something like "if the difference between the data point is greater or lesser than the mean of the two cells either side by 0.003 put check or the value in the cell at the end of the row". I am not sure how to program a running mean over the row though. Any help most appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1
2																																		mean	max row	min row	max-mean	min-mean
3	73.37852	73.37859	73.37871	73.37906	73.37889	73.37883	73.37875	73.37871	73.37861	73.37859	73.37846	73.37849	73.37846	73.37847	73.37835	73.37835	73.37831	73.37834	73.37827	73.37824	73.37824	73.3782	73.37812	73.37806	73.37808	73.37804	73.378	73.37795	73.39888	73.37786	1	0		73.37908	73.39888	73.37786	0.019796	0.001219	check
4	73.37907	73.37914	73.37919	73.37912	73.3791	73.37901	73.37896	73.37886	73.3788	73.37875	73.3787	73.37857	73.37853	73.37843	73.37836	73.37811	73.37824	73.3782	73.37815	73.37811	73.37809	73.37801	73.37793	73.37789	73.37788	73.37786	73.37782	73.37786	73.37778	73.37777	0	1		73.37841	73.37919	73.37777	0.00078	0.00064
5	73.37789	73.37795	73.37798	73.37795	73.37799	73.37799	73.37788	73.37793	73.37798	73.37801	73.378	73.378	73.37803	73.37808	73.37808	73.37806	73.37808	73.37808	73.37801	73.37791	73.378	73.37808	73.37804	73.38511	73.37814	73.37811	73.37814	73.3782	73.37811	73.37813	1	0		73.37826	73.38511	73.37788	0.006842	0.000387	check
6	73.37719	73.37728	73.37716	73.37722	73.37721	73.37715	73.37718	73.37698	73.377	73.37713	73.37714	73.3773	73.37727	73.37721	73.37725	73.37727	73.37722	73.37729	73.37734	73.37726	73.37727	73.37727	73.3771	73.37721	73.37707	73.37722	73.37723	73.37722	73.37724	73.3772	0	1		73.3772	73.37734	73.37698	0.000133	0.000218
7	73.37746	73.37748	73.37739	73.37734	73.37724	73.37713	73.37695	73.37713	73.37713	73.37708	73.37708	73.37708	73.37709	73.37717	73.37719	73.37722	73.37727	73.37724	73.37728	73.3773	73.37725	73.37723	73.37726	73.37735	73.37729	73.37725	73.37724	73.37706	73.37719	73.37716	1	0		73.37722	73.37748	73.37695	0.000264	0.000271
Sheet1

Cell Formulas
Range	Formula
AH3:AH7	AH3	=AVERAGE(A3:AD3)
AI3:AI7	AI3	=MAX(A3:AD3)
AJ3:AJ7	AJ3	=MIN(A3:AD3)
AK3:AK7	AK3	=ABS(AI3-AH3)
AL3:AL7	AL3	=ABS(AJ3-AH3)
AM3:AM7	AM3	=IF(OR(AK3 > 0.0015,AL3<-0.0015),"check","")

 

[offthelip]

try running this code which will colour cells red which are out of tolerance using the rule you suggested

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 30))
For i = 3 To lastrow
For j = 2 To 29
  mecells = (inarr(i, j - 1) + inarr(i, j + 1)) / 2
  Delta = Abs(mecells - inarr(i, j))
  If Delta >= 0.003 Then
   With Range(Cells(i, j), Cells(i, j)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
  End If
Next j
Next i
End Sub