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

yes High and Low will be the better option. My aim was to have the structure first to be able to test different scenarios. But if you can create concept for H and L that would be a huge! For certain I would like to learn how to do it in excel

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.

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

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:

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

Return values between time range

Anonymous

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

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

46 answers

Anonymous

2024-03-23T16:09:18+00:00

yes High and Low will be the better option. My aim was to have the structure first to be able to test different scenarios.

But if you can create concept for H and L that would be a huge!

For certain I would like to learn how to do it in excel
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-23T15:48:44+00:00

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.
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-23T12:45:12+00:00

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
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-23T12:16:01+00:00

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:
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-23T11:25:15+00:00

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

Why?
Was this answer helpful?

0 comments No comments