Find max and min value based on time criteria

[dorian_810]

hello, im trying to find the max and min of
a range between a specific time period and
have this repeat at this time period giving the max/min
for each individual period. EX: column A is Date,
column B is Time , column C is open price. D is High price,
E is Low price, F is Close price. I want to find the max/min or high/low of price
between 9:00am and 12:00pm on 7/12/2010 and have the max/min price shown
in column J:1/K:1. Then have this repeat for next day 7/13/2010 and results in J:2/K:2
Thanks for any help you can provide.

note:
A date B time C open D high E low F close G volume

 

[Nogslaw]

Welcome to the Board!

Try using something like this for your MAX:

=MAX(IF(A2:A10 = DATEVALUE("7/12/2010"), IF(B2:B10>TIMEVALUE("9:00:00 AM"), IF(B2:B10<=TIMEVALUE("12:00:00 PM"), D2:D10, 0), 0),0))

and something like this for your MIN:

=MIN(IF(A2:A10 = DATEVALUE("7/12/2010"), IF(B2:B10>TIMEVALUE("9:00:00 AM"), IF(B2:B10<=TIMEVALUE("12:00:00 PM"), E2:E10))))

Both formulas are array formulas and must be entered with CTRL+SHFT+ENTER instead of just ENTER. If you do it correctly, the formula bar should automatically insert/display curly braces around the formula for you {}. Do not type them in, they appear when the CSE is pressed.

Just a side note, you may wish to have your reference dates (7/12/2010, 7/13/2010, etc.) in cells next to the formulas. Then you could easily refer to them in the formula instead of the hard-coded dates. This would allow you to update them easier. Also, referencing the times in cells could also make it easier to update if you ever need to change the times in several cells.

 

[dorian_810]

Hi Nogslaw.

Thanks for the help. I've been playing
around with the code you provided and it
seems to work well.

The only problem is i need to find a way to
have it repeat for every day.
EX: i need to pull the max/min for monday at
9 am to 12pm and post these values in a cell (d1).
Then tuesday find max/min at 9am to 12pm and post
these values in d2. And the next day and so on until
the end of the data list is reached.

I hope this makes sense. Agian thanks for the help.

 

[Nogslaw]

The easiest way is going to be to place the date in an adjacent cell, then reference that date in the formula.

For example, in cell L1, place the date 7/12/2010. Then use this modified formula for your MAX in J1:

=MAX(IF($A$2:$A$10 = L1, IF($B$2:$B$10>TIMEVALUE("9:00:00 AM"), IF($B$2:$B$10<=TIMEVALUE("12:00:00 PM"), $D$2:$D$10, 0), 0),0))

and this one for your min in K1:

=MIN(IF($A$2:$A$10 = L1, IF($B$2:$B$10>TIMEVALUE("9:00:00 AM"), IF($B$2:$B$10<=TIMEVALUE("12:00:00 PM"), $E$2:$E$10))))

You can copy the code down the column and when you add each day's date into the adjacent cell in column L it will update for that date.