My new version of outlier's highlighting VBA
Sub outliers_IQR()
Dim Rng As Range, rTest As Range: Set rTest = Selection
Dim rQ1 As Double: rQ1 = WorksheetFunction.Quartile(rTest, 1)
Dim rQ3 As Double: rQ3 = WorksheetFunction.Quartile(rTest, 3)
Dim IQR As Double: IQR = Q3 - Q1
Dim Factor As Double: Factor = 2.2
For Each Rng In rTest
If Rng > (rQ3 + Factor * IQR) Or Rng < (rQ1 - Factor * IQR) Then
Rng.Interior.Color = RGB(255, 0, 0) '.Value = "Outlier" 'or delete the data with Rng.clearcontents
Else
Rng.Interior.Color = xlNone
End If
Next
End Sub
What about skewness? The proposal to deal with it can be found here: https://wis.kuleuven.be/stat/robust/papers/2008/outlierdetectionskeweddata-revision.pdf
I need to decipher this math and implement it above.
Some related reading:
http://www.real-statistics.com/students-t-distribution/identifying-outliers-using-t-distribution/grubbs-test/
http://datapigtechnologies.com/blog/index.php/highlighting-outliers-in-your-data-with-the-tukey-method/
http://stats.stackexchange.com/questions/60235/how-accurate-is-iqr-for-detecting-outliers
Brak komentarzy:
Prześlij komentarz