Dynamic Average Lowest 4 of first 9 Integers

I will be the first to admit that I am not proficient when it comes to (semi-)complex Excel Worksheet Functions. I usually wind up creating a UDF (User-Defined Function).

What’s an Excel User Defined Function?

A User Defined Function is a procedure (a group of commands) written in VBA that (usually) accepts inputs and returns a result. A UDF cannot modify the formatting of a cell or workbook or move values around on a worksheet.

Basically, UDF’s enable you to create custom functions that act very similarly to the built-in functions that are included in every installation of Excel, such as SQRT, SUM, and MAX.

Keep in mind that UDFs generally do have some drawbacks, such as:

• You are now required to save your workbook as a Macro-Enabled Workbook, even if you do not have any other code.
• UDFs are usually anywhere from unnoticeably to miserably slower than your typical worksheet function, depending on how well it's created.
• Some minimal knowledge of how to use the VBE is required to perform an action such as a simple "copy/paste" of the function into the editor.

So now you have a generalized idea of what a UDF is, let's get to the code itself. For most UDFs, I generally recommend that you do not use loops if possible. However, since you are only wanting to calculate 9 cells, I don't think you will see any performance issues.

1. Go ahead and save your workbook as a Macro-Enabled Workbook to start things off. Assuming your workbook was already saved, this essentially creates a backup of the pre-UDF attempts
2. Open up the VBA Editor. There are many ways to do this, but I just press Alt + F11
3. You should see a navigation bar on the left side of the editor, this is called the Project Explorer (PE). If you do not, simply press Ctrl + R in the VBE
4. In the PE, right-click on VBAProject (Your WB Name Here.xlsm), then find Insert, and click on Module
   -- You should now see Module1 highlighted in the PE
5. Paste the code from below to the editor to the right

Option Explicit

Public Function LOWAVE(Rng As Range, Quantity As Long, Optional ErrorOnQuantity As Boolean = False) As Variant

    Dim SortedArr() As Double, UnsortedArr() As Variant
    Dim UnsortedIndex As Long, SortedIndex As Long, FoundIndex As Long, i As Long
    Dim FoundInit As Boolean
    
    If Rng.Rows.Count = 1 And Rng.Columns.Count > 1 Then
        ' Convert a single-row/multi-column array to a 1-D array
        UnsortedArr = Application.Transpose(Rng.Value)
    ElseIf Rng.Rows.Count > 1 And Rng.Columns.Count > 1 Then
        ' Converts a multi-dimensional array to a 1-D array
        Dim multDimArr() As Variant
        multDimArr = Rng.Value
        ReDim UnsortedArr(1 To Rng.Rows.Count * Rng.Columns.Count, 1)
        Dim x As Long, y As Long, z As Long
        For x = LBound(Rng.Value, 1) To UBound(Rng.Value, 1)
            For y = LBound(Rng.Value, 2) To UBound(Rng.Value, 2)
                z = z + 1
                UnsortedArr(z, 1) = multDimArr(x, y)
            Next y
        Next x
    Else
        ' If a single column is used, then it's already a 1-D array!
        UnsortedArr = Rng.Value
        Debug.Print UnsortedArr(1, 1)
    End If
    
    ReDim SortedArr(Quantity - 1)
    
    ' Adds support for multi-dimensional arrays
    For UnsortedIndex = LBound(UnsortedArr) To Quantity
    
        ' Set default values for each iteration
        FoundInit = False
        FoundIndex = -1
        
        For i = LBound(UnsortedArr) To UBound(UnsortedArr)
        
            If Not IsEmpty(UnsortedArr(i, 1)) Then
                
                If Not FoundInit Then
                    ' Initialize the array element with the first numerical value
                    SortedArr(SortedIndex) = UnsortedArr(i, 1)
                    FoundInit = True
                    FoundIndex = i
                ElseIf UnsortedArr(i, 1) < SortedArr(SortedIndex) Then
                    ' Replace with values smaller than the current value
                    SortedArr(SortedIndex) = UnsortedArr(i, 1)
                    FoundIndex = i  ' Saves location to 'Empty' the element later
                End If
                
            End If
            
        Next i
        
        If Not FoundInit Then
            ' No more numerical values found and is below your 'Quantity' argument
            If ErrorOnQuantity Then
                LOWAVE = CVErr(xlErrNum)
                Exit Function
            Else
                ReDim Preserve SortedArr(SortedIndex - 1)
                Exit For
            End If
        ElseIf SortedIndex = UBound(SortedArr) Then
            ' You've met your max quantity
            Exit For
        Else
            ' After using the value, Empty array element so it's not used again
            UnsortedArr(FoundIndex, 1) = Empty
            SortedIndex = SortedIndex + 1
        End If
        
    Next UnsortedIndex
                    
    LOWAVE = Application.WorksheetFunction.Average(SortedArr)
    
End Function

If you've successfully made it this far, then the hardest part is finished! Now, you just need to use your new UDF in your workbook. Before you do so, you need to understand the arguments and their meanings. The problem with UDFs, they don't give you tips like normal worksheet functions do.

Arguments for LOWAVE() [Low-Average] UDF

Rng [Range Type] Required

This is the range of cells you want to be considered for evaluation. In your specific case, you would enter: A1:A9

Quantity [Long (Numerical) Type] Required

This is the number of cells with the lowest-value cells in your range to average. In your case, you would enter 4

ErrorOnQuantity [Boolean (True/False) Type] Optional

If you set this to True, then if the number of numerical cells in your range is less than the number you used for the Quantity argument above, it will return the #Num! error. Otherwise, it will average all the numerical values it was able to obtain.
For Example, you select a range of 6 cells, and a quantity of 4 cells. However, only 3 of the 6 cells in your range has a numerical value (which is less than your 4). This will return an error instead of simply averaging the 3 cells. Remember, this is an optional argument, meaning you don't have to type True/False in your formula. The default value is False if you type nothing.

So now you know the arguments, this is how you would enter the function in your worksheet:

=LOWAVE(A1:A9, 4) or if you want to use the ErrorOnQuantity: =LOWAVE(A1:A9, 4, TRUE)

Hope this helps you and anyone else who may be looking for a similar solution. Perhaps someone with more worksheet formula knowledge may be able to provide an alternate method for those who prefer it. UDFs are much more customizable so some people prefer one over the other depending on their specific needs.

You can make use of the AGGREGATE function which has an option to ignore errors:

=AVERAGE(SMALL(INDEX(myRange,,AGGREGATE(15,6,COLUMN(myRange)/ISNUMBER(myRange),{1,2,3,4,5,6,7,8,9})),{1,2,3,4}))

• COLUMN(myRange)/ISNUMBER(myRange) returns an array of error or the column number
• =INDEX(myRange,,AGGREGATE(15,6,COLUMN(myRange)/ISNUMBER(myRange),{1,2,3,4,5,6,7,8,9})) returns the contents of the first nine (9) cells containing numbers.
• SMALL(INDEX(myRange,,AGGREGATE(15,6,COLUMN(myRange)/ISNUMBER(myRange),{1,2,3,4,5,6,7,8,9})),{1,2,3,4}) returns the four (4) smallest values

Then just AVERAGE them.

If you have Excel O365 with the FILTER function, you could use that function instead of AGGREGATE to eliminate the non-numeric values. (And also use the SEQUENCE function for the array constants).

=AVERAGE(SMALL(INDEX(FILTER(myRange,ISNUMBER(myRange)),0,SEQUENCE(9)),SEQUENCE(4)))

EDIT

The above formulas assumed myRange begins in Column A. If it does not, and if you are using the INDEX(...AGGREGATE(... method, then we need to subtract from the array of index columns, the starting column of myRange (minus 1), so as to properly index into the array.

INDEX(COLUMN(myRange),1)-1

eg:

=AVERAGE(SMALL(INDEX(myRange,,AGGREGATE(15,6,COLUMN(myRange)/ISNUMBER(myRange),{1,2,3,4,5,6,7,8,9})-INDEX(COLUMN(myRange),1)+1),{1,2,3,4}))

That is not necessary if you are using the O365 method.