Thread: Maximum value within a 10 minutes timeframe (conditional maximum)

Hi, I'm looking for someone who can help me out with a VBA Macro for the following problem.

There is one column with time running from 3pm to 9pm with the format (hh:mm:ss). Next to it there is a column with numeric values, so that in every row there is a value assigned to a certain point of time.
Time Value
05:10:12 201
05:12:01 212
05:14:11 207
05:15:01 209
05:15:20 220

I need a macro for the third column that gives out the maximum value within 10 minutes in each row, respectively last 10 minutes.

In other words there should be a timeframe (counting 10 minutes) and a maximum function within this timeframe.

Thank You for Your support.

Hi there.

This can be done with normal formulae, with no need to resort to macros.

If I have understood you correctly, you are looking at a window with the reference time at the centre of it, i.e. 5 minutes before or 5 minutes after. If this is not the case, you will need to adjust the +/-TIME command in my formula.

You can use the MATCH command to determine the range you're interested in, then use OFFSET to refer to it. If your time is in column A, and the value in column B, the formula for column C is:

=MAX(OFFSET(B3,MATCH(A3-TIME(0,5,0),A:A)-ROW(A3),0,MATCH(A3+TIME(0,5,0),A:A)-MATCH(A3-TIME(0,5,0),A:A)+1,1))

You need to have a dummy value outside the first 10minute window at the start of the file to avoid errors for the first couple of rows (I have used 8:00 AM, with a data start time of 8:42 AM). This could be avoided using an ISERROR command, but it starts to look really scary then! Cell C2 then becomes:

=MAX(OFFSET(B2,IF(ISERROR(MATCH(A2-TIME(0,5,0),A:A)),0,MATCH(A2-TIME(0,5,0),A:A)-ROW(A2)),0,MATCH(A2+TIME(0,5,0),A:A)-ROW(A2)+1-IF(ISERROR(MATCH(A2-TIME(0,5,0),A:A)),0,MATCH(A2-TIME(0,5,0),A:A)-ROW(A2)),1))
(Note - this is cell F2 in the attached spreadsheet)

In the attached file I have broken the formula down in to several columns, so you can see what it's doing, then provided a final column with all the parts stitched together.

Any questions, give me a shout.

Best regards, Rob.

At first I would like to thank you very much Rob you helped me a lot.
I think I wasn't precise enough in my description. I need a time window with the reference time at the end of it, so that only the rows before become relevant (5min before). I guess that makes the formula less complex concerning the iserror comand, which I can hardly reconstruct:-S
Would you be so kind and help me with this change please?

Hi Jimmy

Revised version attached. The formula includes the ISERROR, and checks up to 10 minutes in to the past. The value 10 appears a few times in the formula - change this to change the time window.

Glad to be of help.

Feel free to press the Star below.

Cheers, Rob.

I have one more question Rob. When defining the starting position of the time window, you typed in +1 in the rows argument of the Offset command. But why? Isn't that one row too much then?

Ok Got it