Snasui Excel Forum

Code: Select all

Option Explicit
Public Function MAE(list As Variant) As Single
Dim item As Variant
Dim ndata As Integer
Dim mynum
Dim mean As Single
ndata = 0
    For Each item In list
        ndata = ndata + 1
    Next item

        mean = Application.WorksheetFunction.Average(list)
        MAE = mean
End Function

Code: Select all

Public Function MAE(list As Range) As Single
    Dim item As Range
    Dim ab() As Variant
    Dim i As Integer
    For Each item In list
        i = i + 1
        ReDim Preserve ab(i)
        ab(i) = Abs(list(i))
    Next item
    MAE = Application.Average(ab)
End Function

Code: Select all

Option Explicit
Public Function MAE(list As Range) As Single
    Dim item As Range
    Dim ab() As Variant
    Dim i As Integer
    For Each item In list
        i = i + 1
        ReDim Preserve ab(i)
        ab(i) = Abs(list(i))
    Next item
    MAE = Application.Average(ab)
    Application.ScreenUpdating = False
    With item
        If .i > 1 Then
        ElseIf .Column = 2 And .Offset(1, 0) = "" _
            And .Offset(-1, 0) = "" Then
                .Offset(0, -1).Resize(, 2).Cut
                .End(xlUp).Offset(1, -1) _
                    .Resize(, 2).Insert shift:=xlDown
        End If
    End With
    Application.ScreenUpdating = True

End Function

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error Resume Next
    If UCase(Mid(Target.Formula, 2, 3)) = "MAE" Then
        Range("E1") = Target.Value
        Target.ClearContents
    End If
    Application.EnableEvents = True
End Sub