Return values between time range

Question

Hi, I'm struggling to produce an idea of how to return value and then run certain calculations.

In the example below, I have two columns. Time and numbers

• first, I would like to return values between 11:00:00 and 12:00:00, all the way down (the Tiem column represents 5-minute intervals of 24 hours),

To be more specific I would like to return values for each day, between 10:00:00 and 11:00:00

• second, out of the returned values, I would like to return to the next columns only values that are greater than the top cell.

(the table below is an example of a few thousand data points that represent days)

10:50:00	2369.2
10:55:00	2368.3
11:00:00	2370
11:05:00	2369.2
11:10:00	2368.2
11:15:00	2370.9
11:20:00	2370.2
11:25:00	2366.3
11:30:00	2367
11:35:00	2367.8
11:40:00	2371
11:45:00	2368.7
11:50:00	2367.3
11:55:00	2374.2
12:00:00	2373.3
12:05:00	2367.8
12:10:00	2367.8
12:15:00	2364.4
12:20:00	2361.9

Answer 1

I used to mess around with this sort of stuff a few years ago.

Let's see if I've got this right:

Talking first only about upward price movement; within your chosen interval (50 minutes) you want to know what happens if you buy at the open of that 50 minutes, what the greatest movement up is (and when it happens) before that 50 minutes is over or before the price drops below the buy price, whichever happens sooner?

If this is right, then, if available to you, you could use the Highs and Lows data too. Whether or not you have High/Low data, I'd use a different method.

I'll await confirmation that I've got the concept right.

Answer 2

Yes it’s possible that I made error copying the date into new spreadsheet.

So you are right, the data is open and close of financial instrument on 5 min interval. The concept is to measure the max move from the entry point .

I will check the data once I’m back home

Answer 3

FYI, your dates in column A are all over the place.

I suspect you've pasted data in and Excel has mixed up some of the months with days of the month.

Where are you getting this data from?

It would help me to know what the context of this is. Is it financial instruments and their open and close prices?

In words, what are you aiming to do? It'll be easier for me to produce the output you want if I understand that.

Your dates in the order they appear in:

Answer 4

In the last picture I sent, column E (and F) you have prices in clumps of 5.

Why?

Answer 5

Absolutely. That would be great!