Select value closest to...
 

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.


--
x3mist
------------------------------------------------------------------------
x3mist's Profile: http://www.excelforum.com/member.php...o&userid=36417
View this thread: http://www.excelforum.com/showthread...hreadid=561896

Select value closest to...
 

would this work for you?

My time data was in a6:a15, and corresponding prices in b6:b15. I
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 function


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=561896

Select value closest to...
 

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

"x3mist" wrote in
message ...

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.


--
x3mist
------------------------------------------------------------------------
x3mist's Profile:
http://www.excelforum.com/member.php...o&userid=36417
View this thread: http://www.excelforum.com/showthread...hreadid=561896

Select value closest to...
 

i hope its clearer now that ive uploaded a jpeg showing the problem and
the desired solution. Raw data is the time and price, what is needed
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 |
+-------------------------------------------------------------------+

--
x3mist
------------------------------------------------------------------------
x3mist's Profile: http://www.excelforum.com/member.php...o&userid=36417
View this thread: http://www.excelforum.com/showthread...hreadid=561896

Select value closest to...
 

Here's a sample file:

http://cjoint.com/?hrigq0SBS0

All the formulas needed are array formulas.

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"x3mist" wrote in
message ...

i hope its clearer now that ive uploaded a jpeg showing the problem and
the desired solution. Raw data is the time and price, what is needed
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 |
+-------------------------------------------------------------------+

--
x3mist
------------------------------------------------------------------------
x3mist's Profile:
http://www.excelforum.com/member.php...o&userid=36417
View this thread: http://www.excelforum.com/showthread...hreadid=561896