Find Min and Max in variable length arrays
in [Programming]
|
Prev: select and move columns by their name in header row
Next: select and paste data based on creteria on previous cells
From: Dave on 2 Jun 2010 21:06 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
From: Jacob Skaria on 3 Jun 2010 01:22
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 |