Formula to check a text string and return maximum and minimum values when condition is met

Not really sure how to describe the problem I have.

I have a set of data with dates and times in column A and values in column B.

The dates and times columns are in this format: 02.11.2009 09:00:00.000 GMT-0000

What I'd like is a way to look through each time and date stamp and when it finds 09:00 in the string to return the maximum values from the 07:00, 08:00 and 09:00 rows in column B.

I have attached an example.

Thanks,

Luke

Hi

It is easier to do with 'helper' columns.
In the attached file, formula in column D 'extracts' the 2-digit-Hour vaue from column A using..
=MID(A1,12,2)*1
..the *1 multiples by 1 to convert the extracted text to a numeric value

Then, in 'helper' column E we use this formula..
=AND(D1>6,D1<10)*1
..to check if the numeric value is between 7 and 9 (i.e. >6 and<10 )
Formula returns 0 if it isn't between 7 and 9, and returns 1 if it is.

Then we just use this array-entered formula to get the max value..
{=MAX(B:B*E:E)}

see attached updated file

zeddy
Excel Timing Protocol Engineer

Thanks KOKOSEK that works great. I'm not sure if this is the tidiest way to do it but is it possible to have that formula on each row of the sheet but only display the max and min of the 07:00-09:00 period when it reaches 09:00? Maybe with an IF statement? So if it's not 09:00, do nothing but if it is show the max and min? Hope that makes sense.

Hi

If the timestamp values are always hourly, (i.e. 1 row per hour) then the entry for 9:00am will be preceded by 2 rows, i.e. for 8:00am and 7:00am.

You could then use this formula..
=IF(MID(A3,12,2)="09","MAX: "& MAX(B1:B3)& " ; MIN: " & MIN(B1:B3),"")
..starting in cell [C3] and copied down.

(at 9:00am you are checking the previous 2 rows, so that's why it starts in row 3, to allow for previous 2 rows)

I added another sample set of values for the following day, so the MAX values show on the 9:00am row etc etc etc
see attached

zeddy
Excel Large Animal Accounting Crew