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

It looks very good! I'm trying to wrap my head around it. The one thing that I'm worried about is that I could mislead, or we could misunderstand each other, about "time interval." The way I understand it now is that we test every 50 minutes. (pls correct me if I'm wrong) However, I would like to test the specified time interval at a specific time of the day. So, let's say 50 minutes each day at the same time of the day. For example, start at 09:00 and end at 09:50 every day. I apologize if it comes from my side. That formula in the M column, ohh my ohh my. I don't even know where to start. I wasn't expecting it will be so complex and difficult

The linked-to file below is your data from your Book1.xlsx but with corrected dates/times. It's a mess because it's work in progress (I put formulae I'm experimenting with in separate columns so that I can see where they might be going wrong, before trying to put them all together into as few formulae as possible (often only one formula ultimately)). This is only the long trades. In this sheet, the cell F1 (green) is an input cell for you to input the interval you want to explore (50 minutes at the moment). The sheet should recalculate (slowly-at the moment!; don't use 2 minutes - it takes ages to recalculate). The results are showing in columns N:O at the first row of each interval. Very few positive results because most trades are thrown out on the first bar of the interval because the low is lower than the open. I recommend putting calculation to manual to prevent repeated (slow) recalculations. I've hidden columns H:M. These are single cell formulae which process all the rows. The results in columns N:O are formulae in column N which spills one cell to the right, which go down the whole sheet. The workbook: https://app.box.com/s/gj9x1psxrmmojnwf0eh0g8wf5z7ynhxb

I would like to know how to extract the data from defined by me time frame and next run calculations which I mentioned in automated way so I can drop it down for entire database (assuming I’ll get correct one) at this stage I don’t know how to do it other than manually

Yes please. What I need is formula or process which I can apply and modify later on

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-25T17:32:28+00:00

It looks very good!

I'm trying to wrap my head around it.

The one thing that I'm worried about is that I could mislead, or we could misunderstand each other, about "time interval." The way I understand it now is that we test every 50 minutes. (pls correct me if I'm wrong)

However, I would like to test the specified time interval at a specific time of the day. So, let's say 50 minutes each day at the same time of the day. For example, start at 09:00 and end at 09:50 every day.

I apologize if it comes from my side.

That formula in the M column, ohh my ohh my. I don't even know where to start.

I wasn't expecting it will be so complex and difficult
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-25T12:33:59+00:00

The linked-to file below is your data from your Book1.xlsx but with corrected dates/times.

It's a mess because it's work in progress (I put formulae I'm experimenting with in separate columns so that I can see where they might be going wrong, before trying to put them all together into as few formulae as possible (often only one formula ultimately)).

This is only the long trades.

In this sheet, the cell F1 (green) is an input cell for you to input the interval you want to explore (50 minutes at the moment). The sheet should recalculate (slowly-at the moment!; don't use 2 minutes - it takes ages to recalculate).

The results are showing in columns N:O at the first row of each interval. Very few positive results because most trades are thrown out on the first bar of the interval because the low is lower than the open.

I recommend putting calculation to manual to prevent repeated (slow) recalculations.

I've hidden columns H:M. These are single cell formulae which process all the rows.

The results in columns N:O are formulae in column N which spills one cell to the right, which go down the whole sheet.

The workbook: https://app.box.com/s/gj9x1psxrmmojnwf0eh0g8wf5z7ynhxb
Was this answer helpful?

0 comments No comments
Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more
Anonymous

2024-03-24T17:14:34+00:00

I would like to know how to extract the data from defined by me time frame and next run calculations which I mentioned in automated way so I can drop it down for entire database (assuming I’ll get correct one) at this stage I don’t know how to do it other than manually
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-24T16:47:47+00:00

Yes please. What I need is formula or process which I can apply and modify later on
Was this answer helpful?

0 comments No comments