SelectAv Function

Suppose you have a spreadsheet range containing numerical data, and would like to find the average for a number of the most recent values, ignoring the highest (and/or lowest) values from the selected set.

If you like using long array formulas on the spreadsheet, or just want to avoid using VBA, then the solution can be found at: Daily Dose of Excel.

On the other hand, if you would prefer a User Defined Function (UDF) to do the job, here is one that will do it:

Function SelectAv(DataRange As Variant, Optional SelectFrom As Long, _
Optional DiscardHigh As Long, Optional DiscardLow As Long) As Variant

Dim NumRows As Long, NumCols As Long, ResA() As Double, MaxVal As Variant
Dim MinVal As Variant, Val As Variant, ValA() As Variant, Total As Double, Count As Long
Dim i As Long, j As Long, k As Long, Discardk As Long
Const MaxFloat As Double = 1.79769313486231E+308, MinFloat As Double = -1.79769313486231E+308

    If TypeName(DataRange) = "Range" Then DataRange = DataRange.Value2
    NumRows = UBound(DataRange)
    NumCols = UBound(DataRange, 2)
    If SelectFrom = 0 Then SelectFrom = NumCols

    ReDim ResA(1 To NumRows, 1 To 1)
    For i = 1 To NumRows
        ReDim ValA(1 To SelectFrom)
        MaxVal = ""
        MinVal = ""
        Total = 0
        Count = 1

        ' Extract Selectfrom values, starting from right hand end
        For j = NumCols To 1 Step -1
            Val = DataRange(i, j)
            If Val <> "" Then
                ValA(Count) = Val
                  Count = Count + 1
                If Count > SelectFrom Then Exit For
            End If
        Next j
        Count = Count - 1
        ' Discard highest and/or lowest value(s) if required
        If Count > SelectFrom - DiscardLow - DiscardHigh Then
            If DiscardHigh > 0 Then
                For j = 1 To DiscardHigh
                    MaxVal = MinFloat
                    Discardk = 1
                    For k = 1 To SelectFrom
                        If ValA(k) <> "" And ValA(k) > MaxVal Then
                            MaxVal = ValA(k)
                            Discardk = k
                        End If
                    Next k
                    ValA(Discardk) = ""
                Next j
            End If
            If DiscardLow > 0 Then

                For j = 1 To DiscardLow
                    MinVal = MaxFloat
                    Discardk = 1
                    For k = 1 To SelectFrom
                        If ValA(k) <> "" And ValA(k) < MinVal Then
                            MinVal = ValA(k)
                            Discardk = k
                        End If
                    Next k
                    ValA(Discardk) = ""
                Next j
            End If
        End If
        ResA(i, 1) = WorksheetFunction.Average(ValA)

    Next i
    SelectAv = ResA
End Function

Results (using the same data as the DDofE example) are shown in the screenshot below:

SelectAv function finding the average of the best (lowest) four out of the 5 most recent scores.

SelectAv function finding the average of the best (lowest) four out of the 5 most recent scores.

The function arguments are:

=SelectAv(DataRange, SelectFrom , DiscardHigh , DiscardLow)

  • DataRange: Spreadsheet range containing the data
  • SelectFrom (optional): number of values, from the right, to be included.  Blank cells are ignored.
  • DiscardHigh (optional): Number of high values to be excluded from the average
  • DiscardLow (optional): Number of low values to be excluded from the average

If all optional values are omitted the function will return the same as the built in Average() function, except that it returns a column array, with one value for each row of the selected data range.  The function must be entered as an array function, using Ctrl-shift-enter.  See for details.

The example spreadsheet (including open source code) can be downloaded from: SelectAv.xlsb

This entry was posted in Excel, Maths, UDFs, VBA and tagged , , , . Bookmark the permalink.

3 Responses to SelectAv Function

  1. kalx says:

    Some guy called Kenneth Iverson thought about this sort of problem in the 60’s. He felt Fortran was not expressive enough and came up with A Programming Language. My clients use the latest derivative of that, kdb, for handling large amounts of tick data.
    It is all about factoring problems down to more primitive operations. Your problem could be solved with something like:

    AVERAGE(DROP(-hi, DROP(lo, SORT(Range))))

    where AVERAGE is the built-in Excel function, DROP(n, Range) removes the first n if n > 0 and last -n if n < 0, and SORT puts the range in increasing order. Of course DROP and SORT are functions that don't exist in Excel.


    • dougaj4 says:

      But it would be easy to add DROP and SORT functions if you wanted them. In fact I already have a SORT function, so I’d only need the DROP.


  2. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s