Removing Outliers with standard deviation

[ineedhelpp]

Hello,

I am working on a project and told to remove all outliers by using the ? +- 3? approach. I am wondering how i should go about this? do i need to calculate the mean and all quartiles to remove the outliers or is there a single excel function that can remove the data outside 3 standard deviations?

Thank you for any help!

 

[Kenneth Hobson]

I guess you could run a macro to delete/remove data. If there are less than 30 data points, I normally use sample standard deviation and average. Do that first in two cells and then do a simple =IF().

I normally set extreme outliers if 3 or more standard deviations which is a z rating of 0. e.g.

Function f3SDOutlier(x As Variant, xRange As Variant) As Boolean
  Dim sStdDev As Double, xBar As Double
  xBar = WorksheetFunction.Average(xRange)
  sStdDev = WorksheetFunction.StDev(xRange)
  f3SDOutlier = fRating(x, xBar, sStdDev) = 0
End Function

Function fRating(x As Variant, mean As Variant, sd As Variant) As Integer
  Dim z As Double, s As Integer, r As Integer
  Application.Volatile False
  On Error GoTo ExitFunction
  z = (x - mean) / sd
  s = 1
  If z < 0 Then s = -1
  If x = "" Or IsEmpty(x) Then z = 4
  Select Case True
    Case z < 1
      r = s * 5
    Case z >= 1 And z < 1.5
      r = s * 4
    Case z >= 1.5 And z < 2
      r = s * 3
    Case z >= 2 And z < 2.5
      r = s * 2
    Case z >= 2.5 And z < 3
      r = s
    Case Else
      r = 0
  End Select
  fRating = r
ExitFunction:
End Function

It is easy enough to write a formula array function from there. I normally mark the data in red and then remove them manually if appropriate.

The quartile method is more elaborate.

 

[joeu2004]

I am working on a project and told to remove all outliers by using the u +- 3s approach

Of course, you should do what you are told to do. But unless your data is (expected to be) normally distributed, identifying outliers by +/-sd might not be the right thing to do.

More generally, I prefer to use the IQR method. But I choose +/-1.7IQR in order to approximate +/-3sd for normal distributions.

do i need to calculate the mean and all quartiles to remove the outliers or is there a single excel function that can remove the data outside 3 standard deviations?

Ironically, you do not need to calculate "all quartiles". That would be for the IQR method (and only the 25%ile and 75%ile, and not the mean).

But you do need to calculate the mean (AVERAGE) and standard deviation (STDEVP or STDEV.P) for the +/-3sd method.

Although you could "remove" outliers, it might be sufficient to ignore them in your calculations.

For example, if U1 is =AVERAGE(A1:A1000) and S1 is =STDEVP(A1:A1000), where A1:A1000 is all of your data, the mean and standard deviation of the data "without" (ignoring) outliers are the following array-entered formulas (press ctrl+shift+Enter instead of just Enter):

=AVERAGE(IF(ABS(STANDARDIZE(A1:A1000,U1,S1))<=3, A1:A1000))

=STDEVP(IF(ABS(STANDARDIZE(A1:A1000,U1,S1))<=3, A1:A1000))

You might choose to use STDEV instead of STDEVP in the last formula.

PS.... Again, you should do whatever you are told to do. But in actual practice, most statisticians suggest that outliers should be identified, but only removed or ignored after determining that they represent errors in the data, not merely extremes.

 

[Kenneth Hobson]

In a blank sheet, you can run the first 3 subs. They create the data in column A, show non-outliers in column B, and mark column A outliers in red.

Sub ColA()  'Random data in column A
  Dim a, i As Long
  i = 10
  a = RndIntPick(1, 100, i)
  ReDim Preserve a(1 To i + 2)
  a(i + 1) = -1000
  a(i + 2) = 120
  Range("A2", Range("A2").End(xlDown)).ClearContents
  Range("A2").Resize(UBound(a)) = WorksheetFunction.Transpose(a)
End Sub

Sub ColB()  'Column A data with outliers removed
  Dim a
   Range("B2", Range("B2").End(xlDown)).ClearContents
  a = NotOutlier3sd(Range("A2", Range("A2").End(xlDown)))
  Range("B2").Resize(UBound(a)) = WorksheetFunction.Transpose(a)
End Sub

Sub Test_Mark3sdoutliersred()
  Mark3sdOutliersRed Range("A2", Range("A2").End(xlDown))
End Sub

Sub Mark3sdOutliersRed(xRange As Range)
  Dim xSStdDev As Double, xMean As Double, v, i As Long
  Dim r As Range, c As Range
  
  xRange.Interior.Color = xlNone
  
  xMean = WorksheetFunction.Average(xRange)
  xSStdDev = WorksheetFunction.StDev(xRange)
  ReDim a(1 To xRange.Count)
  For i = 1 To UBound(a)
    If xRange(i) > xMean + 3 * xSStdDev Or xRange(i) < xMean - 3 * xSStdDev Then
      xRange(i).Interior.Color = vbRed
    End If
  Next i
End Sub

Function NotOutlier3sd(xRange As Range)
  Dim xSStdDev As Double, xMean As Double, v, i As Long, j As Long, a
  
  xMean = WorksheetFunction.Average(xRange)
  xSStdDev = WorksheetFunction.StDev(xRange)
  ReDim a(1 To xRange.Count)
  For i = 1 To UBound(a)
    If xRange(i) <= xMean + 3 * xSStdDev And xRange(i) >= xMean - 3 * xSStdDev Then
      j = j + 1
      a(j) = xRange(i)
    End If
  Next i
  ReDim Preserve a(1 To j)
  NotOutlier3sd = a
End Function

Function RndIntPick(first As Long, last As Long, _
    noPick As Long, Optional bSort As Boolean = False) As Variant
    Dim i As Long, r As Long, Temp As Long, k As Long
    ReDim iArr(first To last) As Variant
    Dim a() As Variant
     
    For i = first To last
        iArr(i) = i
    Next i
     
    Randomize
    For i = 1 To noPick
        r = Int(Rnd() * (last - first + 1 - (i - 1))) + (first + (i - 1))
        Temp = iArr(r)
        iArr(r) = iArr(first + i - 1)
        iArr(first + i - 1) = Temp
    Next i
     
    ReDim Preserve iArr(first To first + noPick - 1)
    ReDim a(1 To noPick)
    For r = 1 To noPick
        a(r) = iArr(LBound(iArr) + r - 1)
    Next r
     
    If bSort = True Then
        RndIntPick = ArrayListSort(a())
    Else
        RndIntPick = a()
    End If
End Function
 
Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True) As Variant
    With CreateObject("System.Collections.ArrayList")
        Dim cl As Variant
        For Each cl In sn
            .Add cl
        Next
         
        .Sort 'Sort ascendending
        If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
        ArrayListSort = .toarray()
    End With
End Function