Filtering data

[ADE2]

Hi

Please could you give me some advice as to how it would be best t
approach this task.


Below i have a table of Currency quotes based on five minut
intervals.

I would like some advice as to how to filter the 5 minute data t
provide the relevant quotes at the end of the 30,60 and 240 minut
intervals,in seperate 5 column blocks
(TIME OPEN HIGH LOW CLOSE)

If i look down the 5 min time column,the first 30 min interval would b
at

22:00 1.8379 1.8380 1.8377 1.8377

which i would then like to copy over to the 30 minute columns,at th
same row height that this times data appeared in the 5 minute table.

30 MIN
TIME OPEN HIGH LOW CLOSE
22:00 1.8379 1.8380 1.8377 1.8377





5 MIN----------------------------------------------30 MIN

TIME OPEN HIGH LOW CLOSE----------TIME---OPEN----HIG
22:10 1.8378 1.8379 1.8377 1.8378
22:05 1.8377 1.8380 1.8376 1.8378
22:00 1.8379 1.8380 1.8377 1.8377
21:55 1.8379 1.8379 1.8376 1.8379
21:50 1.8377 1.8380 1.8377 1.8379
21:45 1.8379 1.8380 1.8377 1.8377
21:40 1.8379 1.8380 1.8378 1.8379
21:35 1.8377 1.8380 1.8377 1.8379
21:30 1.8380 1.8381 1.8376 1.8377
21:25 1.8380 1.8382 1.8379 1.8380
21:20 1.8379 1.8381 1.8377 1.8380
21:15 1.8381 1.8381 1.8376 1.8379
21:10 1.8385 1.8385 1.8380 1.8381
21:05 1.8385 1.8386 1.8383 1.8385
21:00 1.8388 1.8390 1.8385 1.8385
20:55 1.8382 1.8388 1.8382 1.8388
20:50 1.8381 1.8385 1.8381 1.8382
20:45 1.8380 1.8382 1.8380 1.8381
20:40 1.8380 1.8381 1.8380 1.8380
20:35 1.8380 1.8381 1.8379 1.8380
20:30 1.8382 1.8382 1.8380 1.8380
20:25 1.8380 1.8384 1.8380 1.8382
20:20 1.8382 1.8384 1.8380 1.8380
20:15 1.8383 1.8386 1.8382 1.8382
20:10 1.8385 1.8385 1.8383 1.8383
20:05 1.8384 1.8389 1.8384 1.8385
20:00 1.8381 1.8385 1.8380 1.8384
19:55 1.8382 1.8383 1.8381 1.8381
19:50 1.8382 1.8383 1.8381 1.8382
19:45 1.8381 1.8382 1.8380 1.8382
19:40 1.8377 1.8381 1.8377 1.8381
19:35 1.8377 1.8378 1.8375 1.8377
19:30 1.8380 1.8380 1.8377 1.8377
19:25 1.8379 1.8381 1.8378 1.8380
19:20 1.8380 1.8381 1.8378 1.8379
19:15 1.8385 1.8386 1.8380 1.8380
19:10 1.8387 1.8388 1.8382 1.8385
19:05 1.8387 1.8388 1.8386 1.8387
19:00 1.8387 1.8388 1.8385 1.8387
18:55 1.8388 1.8389 1.8386 1.8387
18:50 1.8395 1.8396 1.8386 1.8388
18:45 1.8395 1.8396 1.8390 1.8395
18:40 1.8394 1.8396 1.8394 1.839

 

[ADE2]

Has anybody any ideas as to the most efficient way to approach thi
problem

Thanks Ad

 

[Dave Peterson]

I don't think I'd spread my data into more columns.

But I would add an indicator column that I could use with
Data|filter|autofilter.

I put your data in B:F (times in column B).

Then I put this formula in A2 (headers in row 1):

=TRIM(IF(OR(MINUTE(B2)={0,30}),"30","")&" "&IF(MINUTE(B2)=0,"60","")&
" "&IF(AND(MOD(HOUR(B2),4)=0,MINUTE(B2)=0),"240",""))

(all one cell)

Then I applied data|filter|autofilter to that range.

I could filter using contains "30", "60" or "240"

And my data would show up nicely.

If you really want to put your data into separate columns, you could use a
formula:

(with the time in A2
=IF(OR(MINUTE($a2)={0,30}),b2,"")
and drag to the right 3 more columns.

That's the beginning of the formula I used in my indicator column.

the next set of 4 would look for the second portion.
=if(minute(a2)=0,b2,"")
dragged right 3 columns.

and so forth.

 

[ADE2]

Hi

Below i have a table of Currency quotes based on five minut
intervals.

I would like some advice as to how to filter the 5 minute data t
provide the relevant quote that would be the highest price reache
during the last full 30,60 and 240 minute intervals.

So for example the last full 30 minute high would be taken from value
between 15:05 - 15:30 inclusive,and would be 1.8396, i only need t
extract the high price and place it in a new location.

The last 60 minute high would be taken from values between 14:05
15:00 inclusive,and would be 1.8412, i only need to extract the hig
price and place it in a new location.


I have used the ceiling formula elsewhere in my spreadsheet(show
below) to give me the latest time at the end of the next full 30 minut
time period,maybe it could be modified to extract the relevant data.


=CEILING(BB3,30/1440)

I am also aware of the MAX formula for extracting the high or low pric
from a range of values,i just don't know how to pull it all together.



Thanks for the help

Ade





5 MIN
TIME OPEN HIGH LOW CLOSE
15:50 1.8394 1.8396 1.8393 1.8396
15:45 1.8396 1.8396 1.8393 1.8394
15:40 1.8389 1.8396 1.8389 1.8396
15:35 1.8389 1.8391 1.8389 1.8389
15:30 1.8385 1.8393 1.8385 1.8389
15:25 1.8386 1.8388 1.8383 1.8385
15:20 1.8393 1.8395 1.8386 1.8386
15:15 1.8393 1.8394 1.8393 1.8393
15:10 1.8390 1.8394 1.8390 1.8393
15:05 1.8394 1.8396 1.8390 1.8390
15:00 1.8397 1.8397 1.8393 1.8394
14:55 1.8408 1.8409 1.8397 1.8397
14:50 1.8407 1.8410 1.8404 1.8408
14:45 1.8405 1.8407 1.8404 1.8407
14:40 1.8405 1.8406 1.8404 1.8405
14:35 1.8405 1.8407 1.8404 1.8405
14:30 1.8404 1.8407 1.8403 1.8405
14:25 1.8401 1.8405 1.8401 1.8404
14:20 1.8402 1.8405 1.8401 1.8401
14:15 1.8409 1.8410 1.8402 1.8402
14:10 1.8408 1.8412 1.8407 1.8409
14:05 1.8400 1.8411 1.8400 1.8408
14:00 1.8393 1.8404 1.8393 1.8400
13:55 1.8395 1.8396 1.8390 1.8393
13:50 1.8396 1.8398 1.8394 1.8395
13:45 1.8392 1.8398 1.8392 1.8396
13:40 1.8396 1.8397 1.8392 1.8392
13:35 1.8394 1.8397 1.8394 1.8396
13:30 1.8392 1.8396 1.8390 1.8394
13:25 1.8399 1.8400 1.8389 1.8392
13:20 1.8397 1.8399 1.8396 1.8399
13:15 1.8393 1.8398 1.8392 1.8397
13:10 1.8393 1.8394 1.8391 1.8393
13:05 1.8395 1.8395 1.8393 1.8393
13:00 1.8390 1.8398 1.8390 1.8395
12:55 1.8394 1.8396 1.8390 1.8390
12:50 1.8391 1.8399 1.8391 1.8394
12:45 1.8395 1.8396 1.8388 1.8391
12:40 1.8386 1.8399 1.8384 1.8395
12:35 1.8376 1.8386 1.8376 1.8386
12:30 1.8381 1.8381 1.8375 1.8376
12:25 1.8374 1.8382 1.8373 1.8381
12:20 1.8374 1.8377 1.8373 1.8374
12:15 1.8364 1.8374 1.8362 1.8374
12:10 1.8358 1.8364 1.8354 1.8364
12:05 1.8356 1.8358 1.8354 1.8358
12:00 1.8366 1.8367 1.8356 1.8356
11:55 1.8369 1.8370 1.8365 1.8366
11:50 1.8375 1.8379 1.8368 1.8369
11:45 1.8370 1.8376 1.8368 1.8375
11:40 1.8362 1.8370 1.8360 1.8370
11:35 1.8349 1.8362 1.8346 1.8362
11:30 1.8352 1.8352 1.8348 1.8349
11:25 1.8351 1.8357 1.8351 1.8352
11:20 1.8360 1.8360 1.8351 1.8351
11:15 1.8351 1.8360 1.8350 1.8360
11:10 1.8367 1.8368 1.8351 1.835