Index value by number/text in a range with time format

okay, thanks, my formula is now:

Formula:

Please Login or Register  to view this content.

The data it should find is on a data sheet, the helper sheet is where I have converted 20 jun 2018 9:37 to 937, and the parameter sheet is where I have the cell where I insert what I want to find (in other words 937).
what I get as result now is: #NUM!
What should I insert in the last(highlighted in bold) part of the formula to find the value 3 columns to the left of the value it looks up on the data sheet?

So I try to use the following formula to pick the value that is in the cell I14, meaning it takes the value one cell to the left of where it find 937 the second time.

Formula:

Please Login or Register  to view this content.

This is awesome, thank you very much! Now I don't need any helper coloumn. How would I proceed if I want to extract the value one/two/three cells to the left of the value? I tried to use the offset, but I didn't get it to work...

Amazing thank you so much! One more thing, how can I adjust the formula to say, get the sum of the volume from 4 rows up or down from the specific value? As shown in the example below

Cool, thanks, but it needs to be integrated into the previous formula, and I'm also wondering what do I change in the formula to get the second and third result of 937?

I can not drag it down, it needs to stay in the specific cell, I must change the value somewhere in the formula to change which 937 it should choose. As seen on the picture I use the formula in another spreadsheet, BA7 on the day sheet.
2018-06-25_18-47-03.jpg

What I mean about integrated in the formula, is that formula automatically sum up the 4 values based on the index and look up value, so in B28 it shows the total sum of the four previous volumes it finds from the time, depending on what time I insert at B28 in the parameter sheet

Thank you so much! You have been for great help!

Hi

Do you have the chance to help with a modification of your great formula?

Formula:

Please Login or Register  to view this content.

There are 4 things I want to achieve:
1. Insert the date in one cell at the parameter sheet, and then it gives me the open price of that day(time: 0930)
2. Insert the date in one cell at the parameter sheet, and then it gives me the close price of that day (time: 1600)
2. Insert the date in one cell at the parameter sheet, and then it give me the lowest price that day.
3. Insert the date in one cell at the parameter sheet, and then it give me the highest price that day.

I'm a bit late coming in and stock trading is not my area of expertise, so bear with me if I'm asking for something that should be obvious.

The current formula that kvsrinivasamurthy has provided for you appears to return the low relative to the nth (in the sample, 3rd) occurrence of the specified time.

With your latest request, are you looking for 1 formula that can return the correct result based on the parameters that are defined, or 4 additional formulas, 1 for each possible outcome?

It would be helpful if you entered the parameters into your sample, along with the expected results entered manually so that we ccan see clearer what is expected.

FWIW,

{=IFERROR(INDEX(Data!$D$8:$D$1500,SMALL(IF(TEXT(Data!$G$8:$G$1500,"hh:mm")=TEXT(DOLLARDE(Parameters!$B$23/100,60)/24,"hh:mm"),ROW(Data!$G$8:$G$1500)),3)-ROW(Data!$G$8)+1),"")}

Should be more efficient than the current formula in J10, haven't looked at K10 yet.

Been reading through the thread a bit more, added a few formula to your latest sample file, see if this helps.

Not sure what you were trying to do with the existing formulas in the red cells, but I've added some more efficient versions just below for you to compare results.

Have you looked at the file in post #20?

To vary the nth instance based on the criteria in the helper page, change 3 in the formula (the nth value of small) to the reference of the cell you want to use as criteria.

Yes. The thing is that I can't have a formula that use the nth instance, as sometimes there aren't any more than 1 instance of the price at 1045 (as an example). It needs to use the 1,2 and 3 from the helper page, as sometimes it only find the time 1045 the third day (or the second and third day). I need to be able to tell the formula to use either 1, 2 or 3 from the helper page as a criteria together with a time (that I reference from a cell). Nth instance won't work

1,2 or 3 from a helper cell is an nth instance.

This formula returns the 3rd instance

=IFERROR(INDEX(Data!$D$8:$D$1500,SMALL(IF(TEXT(Data!$G$8:$G$1500,"hh:mm")=TEXT(DOLLARDE(Parameters!$B$23/100,60)/24,"hh:mm"),ROW(Data!$G$8:$G$1500)),3)-ROW(Data!$G$8)+1),"")

This one returns the 1,2 or 3 (or higher if desired) based on what is entered in G5, if G5 is empty, it will default to the 3rd match found.

=IFERROR(INDEX(Data!$D$8:$D$1500,SMALL(IF(TEXT(Data!$G$8:$G$1500,"hh:mm")=TEXT(DOLLARDE(Parameters!$B$23/100,60)/24,"hh:mm"),ROW(Data!$G$8:$G$1500)),IF(G5>0,G5,3))-ROW(Data!$G$8)+1),"")

The highlights show the change.

okay, so I tried to insert the formula in BE7 on the Days sheet, but I only get 0, the correct answer should be 12.5484 which is found in B168 on the data sheet. The nth instance is taken from the D20 on the parameter sheet. See attached workbook.

Now it makes more sense, you want the 3rd day, not the 3rd 1045.

What if 1045 doesn't appear on the 3rd day, nearest time before, or after?

yes, the third day, it will however always appear the third day as I will track it manually by inserting the time based on a stock chart where I now it was traded at that time, however I can not be sure it traded the same time the day before, or two days before, that's why it need to have an option to chose which day I want to track it from (most of the time from day 3). It works like this: I look on the chart of the day, find the time, insert in the time in the parameter boxes and then hit the "get data from google" button on the Parameter tab. Then there is a script that downloads stock data and past it automatically into the Data sheet. The formula on the day sheet looks for the time I inserted and gives me the closing price (other formula gives high,low and so on).

One final question, before I try to make the formula more complicated than it might need to be.

Does the Data tab always contain 3 days worth of data?

Yes, but it doesn't record the data for all the minutes every time, if you try to insert a ticker like IBM in B9 on the Parameter sheet and then press Get data from Google, you can see on the data sheet that it has data for every minute all three days, if you then try to insert ACIU and do the same, then you can see it has less data, meaning some minutes are missing, this in other words means that the first, second and third day starts and ends on different rows on the Data sheet, so to know when it starts the helper sheet is made, and the helper sheet use the first second or third a (of these kind of numbers a1530883800) to give a value of 1, 2 and 3 indicating which day it is.

The Tab data will however later in one instance contain 15 days of data, but that we must take for next step when we have solved the first formula.

Ok, try this one

=IFERROR(INDEX(Data!$B:$B,LOOKUP(1E+100,SMALL(IF(TEXT(Data!$G$8:$G$1500,"hh:mm")=TEXT(DOLLARDE(Parameters!$B$20/100,60)/24,"hh:mm"),ROW(Data!$G$8:$G$1500)),CHOOSE({1,2,3,4},1,2,3,Parameters!D20)))),"")

This will pull 1st, 2nd, 3rd, and defined instances at once, then find last result that is not an error (3rd will error if there are only 2 matches, etc).

So with 2 in D20, the array will be (in this order) 1st, 2nd, 3rd, 2nd If there are 2 or more matches, it will pull the second, if there is only one then it will pull the first.

I'm getting brainfreeze trying to wrap up the logic needed for all possibilities, trying to pull 2nd day when 2nd and 3rd match but 1st doesn't might be an issue, better for you to test, you will know what the results should look like better than me.

okay, with that formula I still get 0 as the result. (its an array formula yes?)
If think the "easiest" solution to the problem is that the formula needs to have a kind of if function that uses both the 1,2 or 3 on the helper page, and the time in G column to index the result from the prices. Take a look of how it is done in AJ7 or AO7 on the day sheet. I think it could be done with something like that, the "only" difference is that there is a second factor that needs to be taken into account, namely the time insert on the parameter sheet.

Yes, it is an array formula. I get the correct result. Do you have to change commas to semicolons before using the formula? If so, then try this one.

=IFERROR(INDEX(Data!$B:$B;LOOKUP(1E+100;SMALL(IF(TEXT(Data!$G$8:$G$1500;"hh:mm")=TEXT(DOLLARDE(Parameters!$B$20/100,60)/24;"hh:mm");ROW(Data!$G$8:$G$1500));CHOOSE({1\2\3\4};1;2;3;Parameters!D20))));"")

Changing commas to semicolons in the array constant transposes the array and changes the functionality.

No, I use commas.
See image for the result I get:
2018-07-08_16-57-54.jpg

Strange, see if it works for you with this copy of the file.
excelnabb.PNG

I get the same result, however it show the right number when I open it, but then excel ask me if I want to enable editing, and I press yes, then the value in the cell change to 0...

That is a new problem

Try changing {1,2,3,4} to {1;2;3;4}

Same result.
I found this, could it be that the formulas are not working until I enable editing?
https://www.syncfusion.com/forums/10...ing-is-clicked

That could be part of it, explaining why you see the correct result to start with, but it still doesn't explain why it gives the wrong result afterwards.

I tried it on another computer, and it works there I will test it some more to check if it behaves the way it suppose to do. And if I want the time, the highest price and so on, the only thing I need to change is this part: =IFERROR(INDEX(Data!$B:$B;LOOKUP ?

Does the computer returning the incorrect results use a different version of excel?

Changing the first range should work to find the other info that you need as long as everything is based on the same criteria of nth instance of time.

Might need a different approach, maybe if kvsrinivasamurthy can appy the same approach to their formula, it will work with your settings.

I'm not able to fix the formula to work with the different settings because I'm not able to recreate the problem. I tried changing my regional settings to match yours, but nothing happened.

Originally Posted by jason.b75

Might need a different approach, maybe if kvsrinivasamurthy can appy the same approach to their formula, it will work with your settings.

I'm not able to fix the formula to work with the different settings because I'm not able to recreate the problem. I tried changing my regional settings to match yours, but nothing happened.

No problem, I just changed mine to English, problem solved Thanks a lot for the help btw!

Have a look at this one.

The maximum and low works perfectly! The Closing price and open price doesn't work correctly. I see that sometimes the first minute is not recorded(meaning it starts at 0931), don't know if this could affect the result. I tested with 19.06.18 and 22.06.18 and so on. I get an #N/A error on close price, and wrong price at open price. The open price needs to go to 15000 as well.

Oops! I had the Open and Close formulas around the wrong way.

Close Formula should be =INDEX(Data!B8:B15000,MATCH(G3+1,Data!G8:G15000))

Open Formula =INDEX(Data!E8:E15000,MATCH(G3,INT(Data!G8:G15000),0))

The Open formula need to be array confirmed.

All of the ranges can be expanded to 15000, I had some of them set smaller while I was checking errors.

PERFECT!!! Thank you so much for all the work you have done!

You're welcome!
Thanks for the feedback.

I'm going to have another look at the problem with the formulas not working with your normal date formatting at some point, will let you know if I find anything.

Can't see why it would be a problem, the date format in your settings is different, but the time format is the same, and the formula that caused the problem only looks at the time.
I did notice a discrepancy with precision in the times, which was why i used the TEXT function to make them more consistent.

Thank you kvsrinivasamurthy

anyone?

Did I provide you with an equivalent formula, and if so does that one return the correct results?

Hopefully the person with the impossible to remember long name is still following this thread and will provide some assistance with the formula that they provided.

Originally Posted by jason.b75

Did I provide you with an equivalent formula, and if so does that one return the correct results?

Hopefully the person with the impossible to remember long name is still following this thread and will provide some assistance with the formula that they provided.

Well yes and no
The formula works for finding the volume for the first minute (see BT8 on Day sheet), but I don't know how I should change it to work for BU8 which is cell F114+F113 on the data sheet, and BV8 which is F114+F113+F112 and so on?

See if this helps.

Originally Posted by jason.b75

Oops! I had the Open and Close formulas around the wrong way.

Close Formula should be =INDEX(Data!B8:B15000,MATCH(G3+1,Data!G8:G15000))

Open Formula =INDEX(Data!E8:E15000,MATCH(G3,INT(Data!G8:G15000),0))

The Open formula need to be array confirmed.

All of the ranges can be expanded to 15000, I had some of them set smaller while I was checking errors.

Any chance this formula could also be used for taking into account the time of the day?
I have this formula in Day!BQ7 that checks if the price in Helper!i2 goes below Day!BE7.
In cell Helper!i2, there is a formula that finds the min value from an array formula I have in G1 to G400 on the helper sheet. These formulas use the value in B22 on the parameter sheet. Normaly the formulas in G1 to G400 works (as an example if IBM is used as a stock), but on some stocks it doesn't, so I'm looking to find another formula that could work.
Maybe the dollare or your index formula above could do the trick?

In short terms, I want to find if the price goes below from the time in Parameters B22 and until close

You say that it works with some, but I'm not sure that it does what you think.

Without seeing it working it is difficult to follow correctly.

In short terms, I want to find if the price goes below from the time in Parameters B22 and until close

Which figure are we using as the base price? Do we take the price at the time in B22, or from somewhere else?

If you first enable the macro for the workbook, then type in IBM or AAPL in Parameters!B9, then hit the "button" to the right that says Get Data from Google it will give values on the Helper sheet. The top cell, G1 on the Helper sheet is the "lowest consolidation price (average)"found in Day!BE7, it then takes all the prices in the D row on the Data sheet from where it found the price in Day!BE7 and return it in the column, all the way to the close. Then I have another formula in helper!i2 that finds the lowest price of this column. The last formula on the Day sheet, the formula in BQ7, checks if the price lowest price (Helper!i2) is below the price in Day!BE7 with 2% or less.

The base price is found in Day!BE7

If you look at DARE (which is the stock in the attached example in post 60, you can see that it finds the price 1.6001 (in the D column) at clock 1012 the last day (day 3). So it SHOULD have given me the rest of the prices from this time and until close in column G on the Helper sheet, but it doesn't, so the formula is not working correctly.

You don't need the 400 formula in golumn G of the helper sheet, just this one in I2

=MIN(INDEX(Data!D:D,MATCH(Day!BE7,Data!D:D,0)):INDEX(Data!D:D,MATCH(1E+100,Data!D:D)))

It lookslike you had the same problem as before, where the formula was looking for the third instance of the time in parameters B22, but failing because there were less instances in the data.

BQ7 uses my formula method which allows for there being less instances, the helper uses kvs* formula method which doesn't allow for it.

oh! this is great! Thank you so much!

Btw, I'm sorry for to bother you so much, but your formula seems to work much better than the previous ones I have. I just found another error with this formula, the ones that finds the highest/lowest price within the first hour /5min. It's the same problem here as with the others, they work on the stocks that have a lot of data recored (like IBM and AAPL), but not on the ones that have few stocks recorded, like CODA.
The formula:

Formula:

Please Login or Register  to view this content.

should give me the highest price within the first hour, but gives me a completely other price that I don't understand anything of... the formula can be found in Day!AP7

No bother, stops the grey matter going rusty

Should that be the first hour / 5 min of any day in the data sheet, or of a specified day / occurence?

Originally Posted by jason.b75

No bother, stops the grey matter going rusty

Should that be the first hour / 5 min of any day in the data sheet, or of a specified day / occurence?

It should be the third Day (3). And I should be able to chose the second Day (2) if I want to as well 🙂

I'll give it some thought, probably not going to get chance to post anything until tomorrow now though.

Originally Posted by jason.b75

I'll give it some thought, probably not going to get chance to post anything until tomorrow now though.

okay
Just a suggestion, maybe it could be possible to use the formula you made previously when adding up the volume for every minute from a certain time? But instead of adding the volume, just give the numbers instead, and then have a normal max/min function? I started on something on the helper page, but I didn't understood the formula completely, so i couldn't find out how to just get the price, and not add them. See attached spreadsheet

I've tried that method, but it's not working, the problem being the number of rows to look at for the first hour is an unkown variable, the methods used in the existing formulas are not able to cope with that.

I've got a non array method that appears to work, but it's a long formula and I don't quite have it right. Need to take a break from it and look again later with fresher eyes.

Originally Posted by jason.b75

I've tried that method, but it's not working, the problem being the number of rows to look at for the first hour is an unkown variable, the methods used in the existing formulas are not able to cope with that.

I've got a non array method that appears to work, but it's a long formula and I don't quite have it right. Need to take a break from it and look again later with fresher eyes.

Okay
Just for information, the 1,2 and 3 on the helper Page are made from where the days starts and ends, this is what the current formula is based on

The formula was getting silly so I added a bit into the helper sheet to isolate the correct rows.

Not sure if I follow correctly, but 59 in the formula below looks at the first hour, based on the minutes counter in column A of the data sheet.
i.e. trading starts at 0930, first hour ends at 1029, secound hour starts at 1030.

=MAX(OFFSET(INDEX(Data!D:D,Helper!C5),,,MATCH(59,OFFSET(INDEX(Data!A:A,Helper!C5),,,Helper!E5))))

I don't think it's working correctly, it found 3.51, and it should have found 3.5468, look at C712 on the data sheet

Simple fix, I had it on the wrong column

=MAX(OFFSET(INDEX(Data!C:C,Helper!C5),,,MATCH(59,OFFSET(INDEX(Data!A:A,Helper!C5),,,Helper!E5))))

A bit confused over one thing, you have AK:BC in the Day sheet under the heading 'Today' but even after refreshing the query, the data appears to only go to the end of the previous day

thank you! Do I change it to:
=MAX(OFFSET(INDEX(Data!C:C,Helper!C5),,,MATCH(5,OFFSET(INDEX(Data!A:A,Helper!C5),,,Helper!E5)))) for max 5 minutes?

=MIN(OFFSET(INDEX(Data!D:D,Helper!D5),,,MATCH(59,OFFSET(INDEX(Data!A:A,Helper!D5),,,Helper!E5)))) for min 60min?

and

=MIN(OFFSET(INDEX(Data!D:D,Helper!D5),,,MATCH(5,OFFSET(INDEX(Data!A:A,Helper!D5),,,Helper!E5)))) for min 5min?

it's because today marked doesn't open before 1530, so it will show previous day (18/07/18) until the marked has open.

Close, but not quite. It should always be Helper!C5

Helper!D5 would look at the end of the day, not the start, although that would need changes to other parts of the formula as well.

MATCH(5 would look at the 6th minute as well, you would need MATCH(4 to look at the first 5. (see my comment in post #72).

I'm going to revisit some of the earlier formulas when I get chance and see if I can simplify them a bit with the helpers. Might take a while though, there are a lot to go through.