Find Min and Max in variable length arrays
 

I have a spreadsheet of 1,247,000 rows displaying prices (Date,
Time,Open,High,Low,Close). I would like to find the lowest Low and the
highest High in various daily time slots (like 937am thru 1017am) to then
test against buying above the highest High and selling below the lowest Low
in the time range selected. I can use Min and Max in fixed array periods but
I stumble badly when I try to make the formula dynamic enough to accept many
different time brackets.
Any help would be greatly appreciated.
Thanks!
Dave LeVeck

Find Min and Max in variable length arrays
 

Assuming you have numerics in ColD..(high)..the below array formula will
return the MAX() based on the conditions...Replace the
startdate/time,enddate/time with cell references..Please note that this is an
array formula. You create array formulas in the same way that you create
other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=MAX(IF((A2:A10=startdate)*(A2:A10<=endDate)*
(B2:B10=starttime)*(B2:B10<=endTime),D2:D10))

Replace MAX() with MIN() for the minimum..

--
Jacob (MVP - Excel)


"Dave" wrote:

I have a spreadsheet of 1,247,000 rows displaying prices (Date,
Time,Open,High,Low,Close). I would like to find the lowest Low and the
highest High in various daily time slots (like 937am thru 1017am) to then
test against buying above the highest High and selling below the lowest Low
in the time range selected. I can use Min and Max in fixed array periods but
I stumble badly when I try to make the formula dynamic enough to accept many
different time brackets.
Any help would be greatly appreciated.
Thanks!
Dave LeVeck