Select value closest to...

X

x3mist

I have a week of tick data, approximately 40000 cells in a column.
(Date, Time, Price)

I need to organise this data into 3 hour blocks, meaning open, close,
high and low within a 3 hour period.

How do I select the value closest to the start on a 3 hour block. For
example if the desired 3hour block starts at 18:00:00 and the nearest
entry is 18:00:34, the price at 18:00:34 is therefore the "open" price
for the 18:00 til 21:00 block. Same case applies for the closing
price.

How do I select the highs and lows in each respective block?

The 3 hour block data should be represented in the next sheet.

Thanks to anyone who can help.
 
D

duane

would this work for you?

My time data was in a6:a15, and corresponding prices in b6:b15.
specified start of window in a20, and close of window in a21

=MAX(IF(A6:A15>=A20,IF(A6:A15<=A21,B6:B15)))

this is an array formula so enter with control+shift+enter

you could do the same with the MIN functio
 
B

Biff

Hi!

Is this data sorted (either ascending or descending) or is it random?

By "nearest" do you mean the time that is equal to or greater than the start
time?

And for the close, you want the time that is equal to or greater than the
start time but less than the close time?

Biff
 
X

x3mist

i hope its clearer now that ive uploaded a jpeg showing the problem an
the desired solution. Raw data is the time and price, what is neede
is the open, close, high and low. Time is ascending. Thanks :

+-------------------------------------------------------------------
|Filename: excel_print_screen.JPG
|Download: http://www.excelforum.com/attachment.php?postid=5042
+-------------------------------------------------------------------
 
Top