## 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, _

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
For j = 1 To DiscardHigh
MaxVal = MinFloat
For k = 1 To SelectFrom
If ValA(k) <> "" And ValA(k) > MaxVal Then
MaxVal = ValA(k)
End If
Next k
Next j
End If

For j = 1 To DiscardLow
MinVal = MaxFloat
For k = 1 To SelectFrom
If ValA(k) <> "" And ValA(k) < MinVal Then
MinVal = ValA(k)
End If
Next k
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.

The function arguments are:

• 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 https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/ for details.

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.

Like

• 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.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.