# How to find a data anomaly in a row



## Ed Harris (May 18, 2022)

Hello,
I am trying to highlight anomalous values in my data by colouring the cell. I was kindly given a macro by offthelip last year which was really usefull but now I have much more data and the problem is that the macro highlights the cells either side of the anomaly as well. I havent been able to improve the macro myself. Is there a better mathematical expression for "mecells" that will select just the anomalous value to highlight? here is the macro and data example.

Sub testyellow()
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 25
 mecells = (inarr(I, j - 1) + inarr(I, j + 1)) / 2
  Delta = Abs(mecells - inarr(I, j))
  If Delta >= 0.004 Then
   With Range(Cells(I, j), Cells(I, j)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 255, 0) 'yellow
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

  End If
Next j
Next I
End Sub

Beam2 2022.xlsmABCDEFGHIJKLMNOPQRSTUVWXY173.415773.4155773.4157673.4154473.4157973.4152573.4148273.4153873.4153173.4153973.4152573.4156373.4150273.4154773.4149273.4154173.4156373.4158473.415273.4156273.4153173.4153773.4151173.4149473.41538273.4131573.4133573.4132473.4131973.4132573.4131273.4130673.4130373.4131873.4130373.4131573.4130273.4131473.4131773.4131173.4131973.4131873.4131173.4131173.4131673.4132573.4130773.4131373.4132873.41306373.4120973.4225573.4119273.4120673.4113573.4122973.4752673.4103273.4122573.4121373.3695573.4121673.4120873.3507173.412373.41273.4124773.4118573.41273.4120773.4124873.4122273.4120673.3683173.41209473.4158673.415573.4153173.4153573.4151673.4153473.4161273.4152573.4152173.4158973.4154773.4152673.4153473.4149573.4154473.4154473.4160273.4151873.4145973.4145773.4145873.4153473.414873.415473.41493573.4154473.4153473.415473.4151873.4152273.4156673.415573.4158673.4154473.4152873.4150873.4155673.4155173.415673.4153973.4148873.415673.4152573.4153173.4150873.4153173.4152373.4157373.4156773.41564678-0.010450.010627-0.000150.000718-0.00095-0.062970.062966-0.064940.001938-0.00012-0.042580.04261-8.4E-05-0.061370.061592-0.00030.000473-0.000620.0001457.6E-050.000412-0.00027-0.00015-0.043760.043777Sheet5Cell FormulasRangeFormulaA8:F8A8=A3-B3G8:Y8G8=G3-F3


----------



## 6StringJazzer (May 18, 2022)

Giving us code that does the wrong thing doesn't help to know what is the _right _thing. Can you please define how to identify the anomaly? I suspect that the code conforms exactly to your definition, but the problem is with your definition. This code colors every cell that has a difference of more than 0.004 from the average of the cells to either side of it. By that definition, all the yellow cells are correctly identified as anomalies.

Also this looks like something that could be done much more simply with conditional formatting, without any VBA code.

$scratch.xlsmABCDEFGHIJKLMNOPQRSTUVWXY173.415773.41557373.4157673.4154473.4157973.4152573.4148273.4153873.4153173.4153973.4152573.4156373.4150273.4154773.4149273.4154173.4156373.4158473.415273.4156273.4153173.4153773.4151173.4149473.41538273.4131573.41334573.4132473.4131973.4132573.4131273.4130673.4130373.4131873.4130373.4131573.4130273.4131473.4131773.4131173.4131973.4131873.4131173.4131173.4131673.4132573.4130773.4131373.4132873.41306373.4120973.42254673.4119273.4120673.4113573.4122973.4752673.4103273.4122573.4121373.3695573.4121673.4120873.3507173.412373.41273.4124773.4118573.41273.4120773.4124873.4122273.4120673.3683173.41209473.4158673.41550473.4153173.4153573.4151673.4153473.4161273.4152573.4152173.4158973.4154773.4152673.4153473.4149573.4154473.4154473.4160273.4151873.4145973.4145773.4145873.4153473.414873.415473.41493573.4154473.41534473.415473.4151873.4152273.4156673.415573.4158673.4154473.4152873.4150873.4155673.4155173.415673.4153973.4148873.415673.4152573.4153173.4150873.4153173.4152373.4157373.4156773.41564AnomalyCells with Conditional FormattingCellConditionCell FormatStop If TrueB1:X8Expression=ABS(B1-AVERAGE(A1,C1))>0.004textNO


----------



## Ed Harris (May 18, 2022)

Yes thanks for you comments. Only the cells I have put a border around should be yellow. For example F3 and H3 are much the same as I3 at 73.412,  73.410 and 73.413 respectively whilst G3 is 73.475 this one is an anomaly because its is 0.063 bigger. The graph below shows the anomalies which can be positive or negative.


----------



## 6StringJazzer (May 18, 2022)

I intuitively understand what you mean but you haven't given a mathematical definition of what an anomaly is.

Based on your qualitative description I would define an anomaly as a data point that more than 0.004 from both of it's neighboring points. I would still use conditional formatting an scrap the code.

If you would rather keep using VBA I can fix the code too.

$scratch.xlsmABCDEFGHIJKLMNOPQRSTUVWXY173.415773.41557373.4157673.4154473.4157973.4152573.4148273.4153873.4153173.4153973.4152573.4156373.4150273.4154773.4149273.4154173.4156373.4158473.415273.4156273.4153173.4153773.4151173.4149473.41538273.4131573.41334573.4132473.4131973.4132573.4131273.4130673.4130373.4131873.4130373.4131573.4130273.4131473.4131773.4131173.4131973.4131873.4131173.4131173.4131673.4132573.4130773.4131373.4132873.41306373.4120973.42254673.4119273.4120673.4113573.4122973.4752673.4103273.4122573.4121373.3695573.4121673.4120873.3507173.412373.41273.4124773.4118573.41273.4120773.4124873.4122273.4120673.3683173.41209473.4158673.41550473.4153173.4153573.4151673.4153473.4161273.4152573.4152173.4158973.4154773.4152673.4153473.4149573.4154473.4154473.4160273.4151873.4145973.4145773.4145873.4153473.414873.415473.41493573.4154473.41534473.415473.4151873.4152273.4156673.415573.4158673.4154473.4152873.4150873.4155673.4155173.415673.4153973.4148873.415673.4152573.4153173.4150873.4153173.4152373.4157373.4156773.41564AnomalyCells with Conditional FormattingCellConditionCell FormatStop If TrueA1:Y8Expression=AND(NOT(ISBLANK(OFFSET(A1,0,1))),ABS(A1-OFFSET(A1,0,1))>0.004,ABS(A1-OFFSET(A1,0,-1))>0.004)textNO


----------



## 6StringJazzer (May 18, 2022)

Here's the code. I strongly suggest using code tags when you post code to preserve the spacing and format.


```
Sub testyellow()

   Dim i As Long, j As Long
   Dim LastRow As Long
   Dim InArr As Variant
   Dim DeltaLeft As Double
   Dim DeltaRight As Double
   
   Const Tolerance = 0.004
   
   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 25
      
         DeltaLeft = Abs(InArr(i, j) - InArr(i, j - 1))
         
         If Not IsEmpty(InArr(i, j + 1)) Then
            DeltaRight = Abs(InArr(i, j) - InArr(i, j + 1))
         Else
            DeltaRight = 0
         End If
         
         If DeltaRight >= Tolerance And DeltaLeft >= Tolerance Then
         With Range(Cells(i, j), Cells(i, j)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = RGB(255, 255, 0) 'yellow
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
         
         End If
      Next j
   Next i

End Sub
```


----------



## Ed Harris (May 19, 2022)

Jeff, It works perfectly, I cant thank you enough for doing this for me. I will now go and apply it to my outstanding four months of data (not all at once ) though over one million cells.
Ed


----------



## Ed Harris (Dec 5, 2022)

6StringJazzer said:


> Here's the code. I strongly suggest using code tags when you post code to preserve the spacing and format.
> 
> 
> ```
> ...




```

```


```

```

Hi Jeff, this code you made has been working perfectly but lately I have tried to adapt it again to another range of data Columns 1440 to 2880 but it says the inarr variable is "subscript out of range".  I have tried adjusting the ranges but cannot find what's wrong. Any ideas?


----------



## 6StringJazzer (Dec 5, 2022)

Can you show the current version of the code that shows what you changed for this adaptation? Not sure what you tried to do in your post but the two code sections are blank.


----------



## Ed Harris (Dec 6, 2022)

```
[CODE=vba]Sub b1testyellowHalfHr() ' searches for cells that are higher or lower than their row neighbours and colours them yellow
' for columns 1440 to 2880. REMOVE TEXT FIRST

   Dim i As Long, j As Long
   Dim LastRow As Long
   Dim InArr As Variant
   Dim DeltaLeft As Variant
   Dim DeltaRight As Double
   
   Const Tolerance = 0.004
   
   LastRow = Cells(Rows.Count, "A").End(xlUp).Row
   InArr = Range(Cells(1, 1440), Cells(LastRow, 2880))
   
   
   For i = 3 To LastRow
      For j = 1440 To 2880
      
         DeltaLeft = Abs(InArr(i, j) - InArr(i, j - 1))
         
         If Not IsEmpty(InArr(i, j + 1)) Then
            DeltaRight = Abs(InArr(i, j) - InArr(i, j + 1))
         Else
            DeltaRight = 0
         End If
         
         If DeltaRight >= Tolerance And DeltaLeft >= Tolerance Then
         With Range(Cells(i, j), Cells(i, j)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = RGB(255, 255, 0) 'yellow
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
         
         End If
      Next j
   Next i

End Sub
```

[/CODE]
Either side of the data in this case are ones or dates. For other data sets I set the "For J"  to one cell inside the data array and that made it work but it still found anomalys at the edge - which I dont understand.


----------



## 6StringJazzer (Dec 6, 2022)

```
InArr = Range(Cells(1, 1440), Cells(LastRow, 2880))
```
This will assign InArr to have cell values from rows 1440 to 2880. This is 1441 rows so InArr will have rows 1 - 1441. However, you are trying to index it from 1440 to 2880

```
For j = 1440 To 2880
```
As soon as j reaches 1442 it will raise an error.

You cannot use the same j to reference cell rows and also rows in the array InArr as in the two lines below

```
DeltaRight = Abs(InArr(i, j) - InArr(i, j + 1))
```


```
With Range(Cells(i, j), Cells(i, j)).Interior
```
If you want `j` to refer to the row numbers of cells, you will have to use `j - 1439` to index the array.


----------



## Ed Harris (May 18, 2022)

Hello,
I am trying to highlight anomalous values in my data by colouring the cell. I was kindly given a macro by offthelip last year which was really usefull but now I have much more data and the problem is that the macro highlights the cells either side of the anomaly as well. I havent been able to improve the macro myself. Is there a better mathematical expression for "mecells" that will select just the anomalous value to highlight? here is the macro and data example.

Sub testyellow()
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 25
 mecells = (inarr(I, j - 1) + inarr(I, j + 1)) / 2
  Delta = Abs(mecells - inarr(I, j))
  If Delta >= 0.004 Then
   With Range(Cells(I, j), Cells(I, j)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 255, 0) 'yellow
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

  End If
Next j
Next I
End Sub

Beam2 2022.xlsmABCDEFGHIJKLMNOPQRSTUVWXY173.415773.4155773.4157673.4154473.4157973.4152573.4148273.4153873.4153173.4153973.4152573.4156373.4150273.4154773.4149273.4154173.4156373.4158473.415273.4156273.4153173.4153773.4151173.4149473.41538273.4131573.4133573.4132473.4131973.4132573.4131273.4130673.4130373.4131873.4130373.4131573.4130273.4131473.4131773.4131173.4131973.4131873.4131173.4131173.4131673.4132573.4130773.4131373.4132873.41306373.4120973.4225573.4119273.4120673.4113573.4122973.4752673.4103273.4122573.4121373.3695573.4121673.4120873.3507173.412373.41273.4124773.4118573.41273.4120773.4124873.4122273.4120673.3683173.41209473.4158673.415573.4153173.4153573.4151673.4153473.4161273.4152573.4152173.4158973.4154773.4152673.4153473.4149573.4154473.4154473.4160273.4151873.4145973.4145773.4145873.4153473.414873.415473.41493573.4154473.4153473.415473.4151873.4152273.4156673.415573.4158673.4154473.4152873.4150873.4155673.4155173.415673.4153973.4148873.415673.4152573.4153173.4150873.4153173.4152373.4157373.4156773.41564678-0.010450.010627-0.000150.000718-0.00095-0.062970.062966-0.064940.001938-0.00012-0.042580.04261-8.4E-05-0.061370.061592-0.00030.000473-0.000620.0001457.6E-050.000412-0.00027-0.00015-0.043760.043777Sheet5Cell FormulasRangeFormulaA8:F8A8=A3-B3G8:Y8G8=G3-F3


----------



## Ed Harris (Wednesday at 7:25 AM)

Sorry I don't understand, did you mean columns. Its columns 1440 to 2880 that are of interest.


----------



## 6StringJazzer (Wednesday at 10:47 AM)

Yes, sorry. The code correctly uses columns but I used the wrong word when I said "rows". I did mean "columns."


----------

