arrays - Replacing outliers with zeroes in VBA

Question

I want to write function outliers_std(table, std) which replace outliers with zeroes.

Let's define outlier as a variable that satisfies following inequality :

So I want to go through whole table and if any element satisfies inequality above I want to replace it by 0.

My work so far

Function outliers_std(table As Variant, std As Double)
    Dim row As Range
    For Each row In [table].Rows
        If Abs(table(row) - Application.WorksheetFunction.Average(table)) / Application.WorksheetFunction.StDev(table) > std Then
            table(row) = 0
        End If
    Next
    outliers_std = table
End Function

But when trying to run

outliers_std(E10:E14,1)

where E10:E14 is data below

I get error #ARG!

Do you have any idea where is the problem ?

EDIT

I ran code

Function outliers_std(table As Variant, std As Double) As Variant
    Dim row As Range
    For Each row In [table].Rows
        If Abs(row - Application.WorksheetFunction.Average(table)) / Application.WorksheetFunction.StDev(table) > std Then
            table(row) = 0
        End If
    Next
    outliers_std = table
End Function

And still error stays the same. Did I write something incorrectly ?

Answer 1

code:

Function outliers_std(table As Range, std As Double)
    Dim temp() As Variant
    temp = table.Value
    
    Dim mean As Double
    mean = Application.WorksheetFunction.Average(temp)
    
    Dim stdv As Double
    stdv = Application.WorksheetFunction.StDev(temp)
    
    Dim i As Long
    
    For i = 1 To UBound(temp, 1)
        If Abs(temp(i, 1) - mean) / stdv > std Then
            temp(i, 1) = 0
        End If
    Next
    outliers_std = temp
End Function

Now depending on your version one can either put this in one cell and it will spill the results, or they will need to higlight the same number of cells as the table and use Ctrl-Shift-Enter to return all the values.

---

But one does not vba for this if doing it in the worksheet:

=IF(ABS(E10:E14-AVERAGE(E10:E14))/STDEV(E10:E14)>1,0,E10:E14)

Like above depending on one's version it will auto spill or one will need to highlight the same number of cells and use Ctrl-Shift-Enter