larnett
07-29-2010, 09:37 AM
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
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