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:
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 https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/ for details.
The example spreadsheet (including open source code) can be downloaded from: SelectAv.xlsb
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.
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.
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog