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:
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:
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.
Bookmarks