On Jul 25, 2:09 pm, Geoff wrote:
Hi All

I am trying to clean up a huge amount of data for further analysis.

One of the steps is to remove outliers. I have chosen to work with a
normal distribution and so want to delete all values that fall outside
of 3 deviations from the mean.

Application.Average(Application.Index(myArray, 0, c)) (c is the
column of the array)

Gives me the average but I have already deleted non numeric values and
also incorrect duplicate values. VBA for excel fills the empty pieces
in the array with zeros so my average is not correct. If I do this in
excel the average will exclude empty cells but in the array it does
not do this.

Is there a simple way to work around this.

Thank you in advance

Geoff

I'm not sure if this will help but here is one way to delete all of
those zeros so then you can look at just the non-zero values
Dim myCell As Range
For Each myCell In Worksheets("Sheet1").Range("A1:BH100")
If myCell.Value = "0" Then
myCell.Clear
End If
Next myCell

you can change the clear to delete if you want the deleted. Hope it
helps.