Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max length of variable name? | Excel Programming | |||
SUM for columns of variable length | Excel Programming | |||
Return a Variable value length | Excel Worksheet Functions | |||
Find/Copy Variable length range | Excel Programming | |||
Sum a column of variable length? | Excel Discussion (Misc queries) |