Finding the min or max value in a sub range of a reference.

[larnett]

I am looking for a VBA Excel function to simplify the insertion of the following calculation in my spreadsheets. I believe this function has good general use application and a bit surprised it is not native to Excel.

My array of data has a time stamp in the first column; ideal for VLOOKUP. I want to be able to specify a time interval and then extract the maximum value within a subset of the array selected by the time interval.
Implementation with current functions in Excel is ugly to copy and paste.
=MAX(
INDEX(reference,MATCH(time_min,reference_col1,1),col_min):
INDEX(reference,MATCH(time_max,reference_col1,1),col_max)
)

I believe the following VBA code should work but I do not know how to get past the compiler error caused by the ":" required between the two INDEX functions.

Function IMax(reference, time_min, time_max, col_min, col_max)
IndexMax = Application.WorksheetFunction.Max(Application.WorksheetFunction.Index(refer ence, Application.WorksheetFunction.Match(time_min,reference,1), col_min) : Application.WorksheetFunction.Index(reference, Application.WorksheetFunction.Match(time_max,reference,1), col_max))
End Function

[p45cal]

I think the
MATCH(time_min,reference_col1,1)
part of your formula may not be giving you the right row if the time in time_min is not exactly the same as a time stamp in the first column. It will return the row before the one I think you want. Test it out by having that bit of the formula in a separate cell.

Anyway, if I'm wrong, then try this as your function:Function IMax(reference, time_min, time_max, col_min, col_max)
IMax = Evaluate("MAX(INDEX(" & reference.Address(external:=True) & ",MATCH(" & time_min.Value & "," & reference.Columns(1).Address(external:=True) & ",1)," & col_min.Value & "):INDEX(" & reference.Address(external:=True) & ",MATCH(" & time_max.Value & "," & reference.Columns(1).Address(external:=True) & ",1)," & col_max.Value & "))")
End Function
This relies on the time stamps in column A being in order (later as you go down the list I presume?).

However, if I'm right, another worksheet formula would not require the time stamps to be in any order at all:

=MAX((reference_col1>=time_min)*(reference_col1<=time_max)*OFFSET(reference,0,col_min-1,,colmax-col_min+1))

(which has to be array-entered, otherwise it will give a #Value! error), would translate to a UDF thus:

Function IMax2(reference, time_min, time_max, col_min, col_max)
IMax2 = Evaluate("MAX((" & reference.Columns(1).Address(external:=True) & ">=" & time_min.Value & ")*(" & reference.Columns(1).Address(external:=True) & "<=" & time_max.Value & ")*OFFSET(" & reference.Address(external:=True) & ",0," & col_min.Value - 1 & ",," & col_max.Value - col_min.Value + 1 & "))")
End Function

Both UDFs above are used in the same way on the sheet, normally entered.

I would normally have had multiple lines in each UDF, for easier maintenance and debugging, but I did this in haste.