Index, match formula that combines Vlook up formula

**excelnabb** · 12-16-2017, 08:09 AM

Hi

I have this spreadsheet that downloads stockprices from Google. ( http://investexcel.net/free-intraday-stock-data-excel/ ) The name of the stock (ticker) get inserted into parameters, and the prices and timestamps get automatically inserted into the sheet called Data

I have made a new sheet 15 good day where I want to process this data.

Normaly the stock price is recorded for every minute, but that is not the case with all the stocks (meaning it jumps over some minutes). This means the cells of the prices and timestamps changes row position for every stock) (try typing in IBM and press "get data from google" in the parameter sheet, then try ASTC as ticker, and you will see in the Data sheet what I mean)

The only thing I'm certain that is present every time a new stock is inserted, is the letter a in front of the unix code for the day, and since I will only import data for 3 days each time, I can find the first, second and third a in column A on the Data sheet, and then work my way from there to get the data which I want, since the formulas are not now depended on the data to be on the same row for every time the stock change.

What I'm trying to do, is to combine a Look up a value and return cell =INDEX(Data!B8:Data!B25;MATCH("a";Data!A8:Data!A25;0)+0;1)
https://www.extendoffice.com/documen...-or-above.html

and then replace the a in the formula, with a Vlookup Find The First, 2nd Or Nth Match Value In Excel
=INDEX($A$8:$A$1000;SMALL(IF("a"=$A$8:$A$1000;ROW($A$8:$A$1000)-ROW($A$8)+1);1))
https://www.extendoffice.com/documen...2nd-match.html

To get a formula that finds the first a in coloum A in Data sheet (from row 8 and down),the formula then gives me the value to the right cell.
I then want to be able to make changes to this new formula to find:
-The second and third a in coloumn A, to get data for day 2 and 3.
-The lowest/highest value within the first and last hour,
-The same data only with the volume

In the picture below, a lot of rows are hidden to show where the a's are in front of the unix codes.

data.jpg

IF there is a better formula to solve this, then I'm all up for it, I'm not that good with formulas in excel, so it's a lot of cut and paste from different site that pops up from google

Thanks a lot for a great forum btw

**Glenn Kennedy** · 12-16-2017, 08:22 AM

This is probably very simple... but you've made it sound horrendous.

Cut your raw data down to 10-20 rows and manually enter the results that you want to see, where you want to see them; and delete everything elsethat is irrelevant.

Also, amend your profile to show where you are. Regional settings can affect solutuons offered. "earth" is NOT helpful.

**JohnTopley** · 12-16-2017, 08:38 AM

This ..

=INDEX(Data!$B$8:$B$1000,SMALL(IF(LEFT(Data!$A$8:$A$1000,1)="a",ROW(Data!$A$8:$A$1000)-ROW($A$8)+1,""),1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Change 1 to 2 and 3 with for 2nd and 3rd "a" matches

**excelnabb** · 12-16-2017, 09:20 AM

Awesome! This is exactly what I needed, it works great, thanks a lot!!

I have a few more questions:

1. I know how to chose the value up and down from (changing the +1, to -1 and so on), but how do I chose the third cell to the right? I tried adding +1;3, but I just get an error that this is not a formula then. See picture below:

third to the right.jpg

Now as you have made this great formula, I want to expand on it, by finding:
2. The lowest/highest value in the C or D coloumn, based on the first hour of the G Coloum, for each of the days/a's Both the first hour and the last hour.
3. The highest or lowest value in the C or D coloumn between the first a and the second a, and also a formula that finds it between the second and third a, and the last formula to find it after the third a.

high and low for first hour.jpg

**AliGW** · 12-16-2017, 09:27 AM

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**excelnabb** · 12-16-2017, 09:35 AM

Oh, I forgot to add the updated worksheet, it's attached now.

**JohnTopley** · 12-16-2017, 09:52 AM

I cannot reconcile your "first hour" requirement when you have only highlighted data from times of 09:30 to 10:00

To get data in column E

=INDEX(Data!$E$8:$E$1000,SMALL(IF(LEFT(Data!$A$8:$A$1000,1)="a",ROW(Data!$A$8:$A$1000)-ROW($A$8)+1,""),1))

or

=INDEX(Data!$B$8:$E$1000,SMALL(IF(LEFT(Data!$A$8:$A$1000,1)="a",ROW(Data!$A$8:$A$1000)-ROW($A$8)+1,""),2),4)

4 being the 4th column in range B:E

**excelnabb** · 12-16-2017, 10:12 AM

Your absolutly right, I have now marked the first hour as seen in the picture below.
high and low for first hour.jpg

The get data from coloum E worked great

attached is the updated Excel workbook

**JohnTopley** · 12-16-2017, 10:26 AM

I Used a helper column in column H of "Data"

in H8

=IF(LEFT(A8,1)="a",H7+1,H7)

Copy down

in F7 of "15 Good day"

=MAX(IF((Data!$H$8:$H$1000=2)*(MOD(Data!$G$8:$G$1000,1)>=TIME(9,30,0))*(MOD(Data!$G$8:$G$1000,1)<=TIME(10,30,0)),Data!$C$8:$C$1000))

in G7

=MIN(IF((Data!$H$8:$H$1000=2)*(MOD(Data!$G$8:$G$1000,1)>=TIME(9,30,0))*(MOD(Data!$G$8:$G$1000,1)<=TIME(10,30,0)),Data!$D$8:$D$1000))

Enter both as array formulas

Attached has formulas in Row 8

Using the above you should be able to derive the other formulae.

NOTE: I have "hard coded" the Start/end times to illustrate the formula: however I assume we need to obtain the start time from the second "a" entry and then add 1 hour to get the finish time.

Again I might use a helper to do this.

NOTE: You data in columns C:E of "Data" is TEXT so select a column , then Data==>Text to Columns==>Go to Step 3, select "General" (radio button)then "Finish"

**excelnabb** · 12-16-2017, 05:55 PM

Great!

the thing is, that when I type a new ticker in at B9 on the parameter sheet, then all the data in the cells on the Data sheet is replaced with new data, meaning the helper coloumn H will dissapear. So the helper coloum must be on the 15 good day sheet, and it needs to be on a row, and not a coloum as I have a script that records the data from the row 7 (the whole row) and then insert it to row 20, 21 and so on.

I see that the help coloumn H have 1,2 and 3, does these change automatically according to a's? Since the position of the a's (as you mention) and number of prices and times between the them will change depending on what stock is chosen on B9 on the parameter sheet.

And last of all, just to make it even more complicated...

not all stocks have perfect data that goes from 09:30 to to 10:30, some stocks goes from 09:30 to 10:35, or 10:40, so if there is a way to add 60 minutes to 09:30 and record the minimum/highest number in the adjacent cell C and D for that periode, and do the same for the end time, but then only substract 60 minutes from the closing time which is 16:00. Lets say the stock only have price information for 1025 and the next is 1035, then the last price it should evaluate for the highest/lowest price must be 1025. I don't even know if excel can do this, but it would be divine even if its possible!

**JohnTopley** · 12-17-2017, 02:17 AM

This formula works by taking the start from the first entry for the required 'a": in this case the first 2 in column "H" and adds 1 hour to get the finish time.

=MIN(IF((Data!$H$8:$H$1000=2)*(MOD(Data!$G$8:$G$1000,1)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(2,Data!$H$8:$H$1000,0)),1))*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(2,Data!$H$8:$H$1000,0)),1)+(1/24)),Data!$D$8:$D$1000))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

As for the "helper" it has to be in a column BUT it could be in another sheet

See attached with helper in column A of sheet "Helper": you need to ensure start rows in "Helper" and "DatA" are aligned i.e both 8 in attached.

=MAX(IF((Helper!$A$8:$A$2000=2)*(MOD(Data!$G$8:$G$2000,1)>=MOD(INDEX(Data!$G$8:$G$2000,MATCH(2,Helper!$A$8:$A$2000,0)),1))*(MOD(Data!$G$8:$G$2000,1)<=MOD(INDEX(Data!$G$8:$G$2000,MATCH(2,Helper!$A$8:$A$2000,0)),1)+(1/24)),Data!$C$8:$C$2000))

**excelnabb** · 12-17-2017, 08:05 AM

Great

Do I need the 1,2 and 3 on the Data sheet? as I mentioned, ALL of the data on the Data sheet gets deleted when finding a new stock on the Parameters tab when I hit the "Get data from google" button (try typing in IBM, and hit the button, then look at the data sheet, then try to write AAPL and hit the button, and then look at the data sheet). This means that all of the 1,2 and 3 will dissapear. So I can't have any formulas or number that I insert manually (or automatically?) in the Data sheet, as they would not be there for long

Is it possible to reference coloumn A to G maybe in the Helper sheet then?

**JohnTopley** · 12-17-2017, 11:41 AM

No: you do not need them in Data as I moved the helper column to column A of "Helper"!

Delete column H in Data and see what happens.

**excelnabb** · 12-17-2017, 11:44 AM

I have copied all the data from Data sheet to helper, and inserted the row there. When I use the formula I just get 0 as the low of day

**JohnTopley** · 12-17-2017, 11:57 AM

Why are copying all the data from DATA to HELPER?????? Did I do that in the file I gave you ?

All that is required is to leave the "helper" formula in Column A of "Helper" (which references the DATA sheet) to derive the 1, 2 and 3

**excelnabb** · 12-17-2017, 12:11 PM

It's because the formula doesn't seems to work when I change the ticker. I just get 0 for both high and low after changing 2 tickers. Try typing in AAPL, and then IBM, and you end up with 0 and 0 for both high and low. I think that is because the Data sheet is completely reset everytime. I also though that the H coloumn was needed for the formula to work, so to get around this and not lose the H coloumn I referenced everything to the Helper sheet. Apparently it doesn't work with copying everything to the helper sheet either

**JohnTopley** · 12-17-2017, 12:20 PM

If the data in DATA is replaced AND the formula kept untouched in "Helper" it should work, assuming the DATA format does not change i.e.columns are consistent.

To simulate this, I copied/pasted "DATA" to another sheet, CLEARED "DATA" and then copied/pasted the data back into"DATA".

I got correct results in "15 Good Day"

And where do I type"AAPL" ? IBM?

**excelnabb** · 12-17-2017, 12:39 PM

I'm not touching any of the formulas, but I still can't get it to work. Don't know what I'm doing wrong. Here is a short video of it
https://www.dropbox.com/s/rvi87qe3oo...Excel.mp4?dl=0
(I can't seem to upload a zip file on this forum, even though its under 9,77MB )

**JohnTopley** · 12-17-2017, 01:50 PM

This is the formula in row 8 of "Helper" which is copied down as many rows as you want (2000?

=IF(Data!A8="","",IF(LEFT(Data!A8,1)="a",A7+1,A7)))

I cleared DATA, ran the query and got the correct result.

**excelnabb** · 12-17-2017, 02:04 PM

I copied your formula into the helper, but got 0 this time as well, try changing tickers from AAPL, IBM, ASTC. I get 0 every time

**JohnTopley** · 12-17-2017, 02:17 PM

I have just run AAPL/IBM and have results of HIGH 155.11, LOW 154.08

**JohnTopley** · 12-17-2017, 02:20 PM

... ran with AAPL/ASTC and again have results.

**excelnabb** · 12-18-2017, 02:03 PM

The same thing happens again. This is so strange, can it be something with my excel version? I have office 365, english language pack.
Here you can see a video from where I donwload your spreadsheet to I get 0 and 0:
https://www.dropbox.com/s/hhc8vqhs5g...mulas.mp4?dl=0

**JohnTopley** · 12-18-2017, 02:24 PM

As I say, it works fine with me and as I don't have Office 365 there is nothing more I can do.

The only thing I noted was that the "Ticker" entry appeared to case sensitive (UPPER case only)

**excelnabb** · 12-18-2017, 05:32 PM

No problem, you have been more than helpful! thanks a lot, I really appreciate it!

I finally found the solution to why the formulas wasn't working, apparently Office 365 decided to use semicolons ( ; ) instead of commas (,) for separating Excel formulas.
I followed this guide earlier: https://lockone.wordpress.com/2015/0...as-fix-it-now/ to change the List separator from ; to ,
And I thought everything was okay, but apparently my Decimal symbol also use (,) , so there was a conflict there. I changed the decimal symbol to (.), and it fixed the problem. So now all your formulas works perfectly! :D

I feel embarresed to ask you for anything more, but you seems to be the formula God here

and I'm completely blank when it comes to these long formulas you have made so

Hopefully you will help me with these last bits of this puzzle

1. What is the highest and what is the lowest price between second and third a (day 1)
a. What is the time at these prices (Column G)
b. What is the volume at these prices (column F)

2. What is the lowest and what is the highest price within the last hour of the day (between second and third a) (so from 15:00 to 16:00) (could you also show where in the formula I can change it, to say 2 hours instead of 1?)
a. What is the time at these prices (Column G)
b. What is the volume at these prices (column F)

3. What is the volume and time of the highest price within the first hour (Next day morning spike - after third a) I'm already using your formula here to get the price.

3. What is the total volume of day -1? (Sum F between thirst and second a) Total volume of day 0 (Sum F between second and third a)? and total volume of day 1 (Sum F between after third a)?

This all sums it up! :o It probably seems a little overwhelming, but I think it makes more sense when you see the worksheet, as many of the formulas are reused.
Numbers in italic are the ones I have just typed in. I have written a short description beneath the cells that needs the different formulas. I have not inserted all the data for the last day (after third a), as you probably understand what I mean. If you highlight where in the formula I need to change to get the different results, then I can insert most of the formulas myself

Thanks again

**JohnTopley** · 12-18-2017, 05:54 PM

The ";" is a regional setting (Excel-generic) are use of "," vs "." for numeric values.

I will look at your 3 questions tomorrow if I have time.

Please post a file showing expected results for these conditions.

Thank you.

**excelnabb** · 12-19-2017, 07:57 PM

Okay, so I tried to break down your formula too see if I can make some changes to it myself, as I found out I wanted another value,upward or downwards trending volume for the last and first hour of the day, by using: =IF(LINEST(A2:A6)>0,"↑","↓") formula.

Breakdown formula.JPG

Apparently I can't get it to work though :/

**JohnTopley** · 12-20-2017, 02:38 AM

As requested post a file giving expected results.

**excelnabb** · 12-20-2017, 03:32 AM

I have, in post #25. But I will update it with trending formula as well. Here it is

As mention earlier, Numbers in italic are the ones I have just typed in. I have written a short description beneath the cells that needs the different formulas. I have not inserted all the data for the last day (after third a), as you probably understand what I mean.

Thanks a lot!

**AliGW** · 12-20-2017, 03:34 AM

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

**JohnTopley** · 12-20-2017, 10:59 AM

in I7

=MAX(OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),1))

in J7

=MOD(VLOOKUP($I$9,OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),5),5,0),1)

in K7

=VLOOKUP($I$9,OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),5),4,0)

similar in L:N

in P7

=INDEX(Data!$F$8:$F$1000,MATCH(3,Helper!$A$8:$A$1000,0))

in Q7

=SUM(OFFSET(Data!$F$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),1))

in R7

=MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),Data!$D$8:$D$1000))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

No solution for S and T as yet!

Results in attached are row 9

**JohnTopley** · 12-20-2017, 11:13 AM

In R7

=INDEX(Data!D8:D1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000))))

in S7

=MOD(INDEX(Data!G8:G1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000)))),1)

in T7

=INDEX(Data!F8:F1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000))))

All above are array formulas ...

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**excelnabb** · 12-21-2017, 06:06 PM

Thank you very much!

Though I have a problem, every time I try to insert one of these formulas, the VBA script starts to run over and over and fill up all the cell down to row 105 before the I get this run time error and excel freeze. Do you have any clue why this is happening?

out of stack space.JPG

**randy2012** · 12-21-2017, 07:13 PM

Originally Posted by excelnabb

Thank you very much!

Though I have a problem, every time I try to insert one of these formulas, the VBA script starts to run over and over and fill up all the cell down to row 105 before the I get this run time error and excel freeze. Do you have any clue why this is happening?

Attachment 553045

Hello,

I'll be glad to lend a hand on that. Please visit microsoft's explaination on stack space here: https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx

Looks like you ran too much script and the PC is saying "ENOUGH!!!"

My opinion, of course.

**excelnabb** · 12-27-2017, 11:54 AM

Hi

Hope you guys had a wonderfule christmas!

I'v done some testing on the worksheet and the formulas, and it seems that there are some formulas that makes the script on the Day sheet to go in a loop or something (excel crash and goes into run time error) I have checked microsoft explanation of stack space, and I have tested by deleting a lot of formulas and then inserted the new ones that make it crash, and its the same result, so I don't think it has anything to do with the worksheet being too heavy for the computer.

I have also gone through each formula with and without the script, and found out this:

in I7

=MAX(OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),1))
WORKS - but not with script activated on the Day sheet, Excel crash Run time error (loop?) with script activated.

in J7

=MOD(VLOOKUP($I$9,OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),5),5,0),1)
DOESN'T WORK - Get #N/A with script not activated. Excel crash Run time error (loop?) with script activated.

in K7

=VLOOKUP($I$9,OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),5),4,0)

DOESN'T WORK - Get #N/A with script not activated. Excel crash Run time error (loop?) with script activated.

similar in L:N

in P7

=INDEX(Data!$F$8:$F$1000,MATCH(3,Helper!$A$8:$A$1000,0))
WORKS (with some cell modification- FINDS VOLUME

in Q7

=SUM(OFFSET(Data!$F$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),1))
WORKS - but not with script activated on Day sheet, Excel crash Run time error (loop?) with script activated.

in R7

=MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),Data!$D$8:$D$1000))
WORKS - But finds the lowest value within the day, and not the last hour of the day (R7), so I use this formula in L7 instead).

In R7

=INDEX(Data!D8:D1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000))))

DOESN'T WORK - Finds the value at row 8 from counting from a2 and down. It should find the lowest value within the last hour between a2 and a3.

in S7

=MOD(INDEX(Data!G8:G1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000)))),1)
DOESN'T WORK - It finds the time for the value at row 8.

in T7

=INDEX(Data!F8:F1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000))))
DOESN'T WORK - Finds the volume for row 8.

**JohnTopley** · 12-27-2017, 12:44 PM

R7,S7 and T7 must be array-entered: are they?

All worked OK on your "test" file.

**excelnabb** · 12-27-2017, 01:36 PM

Hi

All of them are array formulas.
Here is the latest excel workbook, do you get any of them to work here?

**JohnTopley** · 12-27-2017, 01:44 PM

File is invalid: cannot open it

**excelnabb** · 12-28-2017, 11:41 AM

Try this one

**JohnTopley** · 12-28-2017, 12:26 PM

The only change is to R7

=INDEX(Data!D8:D1000,MIN(IF((Helper!$A$8:$A$1000=2)*(MOD(Data!$G$8:$G$1000,1)<=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1))*(MOD(Data!$G$8:$G$1000,2)>=MOD(INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1),1)-(1/24)),ROW(Data!$D$8:$D$1000)))+1)

I cannot find the the value in I7 (3.855)

It would help if you have the cell address of EACH result.

RUN TIME errors are not a formula problem.

**excelnabb** · 12-28-2017, 01:38 PM

Okay, I have now the cell adress of each result in the cell text below (row 8). I need formulas for all the cells in row 7 that have bold numbers (that include the up/down arrow as well). Remember the cell adress will change everytime I insert a new ticker/stock on the parameter tab, that's why you couldn't find 3.855 (as it was probably for a stock price for an earlier day).
I have also removed the script in the day sheet now, so you can try out the formula yourself to see if they work, without excel crashing from the run time error.

**bebo021999** · 12-29-2017, 03:13 AM

I use sheet "Helper" as main sheet to calculate, storage the data.
Sheet "Day" is being results only.
Sheet "Day", from column W, I dont have your expected results so I could not get it done.
But, I think it is possible to be done.

**excelnabb** · 12-29-2017, 12:51 PM

Hi
Thanks for the suggestion, but I can't get your formulas to the helper sheet to work, I get #N/A when trying to copy the formulas into the Day sheet. I have copied the whole new table you made on the Helper sheet to the exact location in my worksheet.
Attached its the workbook with the both of the scripts activated. One is for the parameter sheet (getting new stock data from google), the other one is on the day sheet (this script copies any changes made on row 7 in day sheet, you can check how this work by typing IBM, ASTC, AAPL or another stock on the parameter sheet, and then press get data from google, then you will see that the Data sheet is wiped out and replaced with new data, the helper sheet change, and the Day sheet records the changes in Row 7 before they are copied and stored automatically in row 20 and below).

**JohnTopley** · 12-30-2017, 01:45 AM

I looked at the formulae for columns R:T in the attached:

in R7

=MIN(OFFSET(Data!$D$8,Helper!$C$9,,Helper!$C$8-Helper!$C$9+1))

in S7

=INDEX(OFFSET(Data!$G$8,Helper!$C$9,,Helper!$C$8-Helper!$C$9+1),MATCH($R$7,OFFSET(Data!$D$8,Helper!$C$9,,Helper!$C$8-Helper!$C$9+1),0))

in T7

=INDEX(OFFSET(Data!$F$8,Helper!$C$9,,Helper!$C$8-Helper!$C$9+1),MATCH($R$7,OFFSET(Data!$D$8,Helper!$C$9,,Helper!$C$8-Helper!$C$9+1),0))

I used helper cells in 2helper2

in B8

=INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1)

in B9

=INDEX(Data!$G$8:$G$1000,MATCH(3,Helper!$A$8:$A$1000,0)-1)-(1/24)

in C8

=MATCH(B8,Data!$G$8:$G$1000,0)

in C9

=MATCH(ROUND(B9,6),Data!$G$8:$G$1000,0)

in the above i found it would only match if i used ROUND: without ir i get an #N/A error

I also note the times in column F of "Data" are computed so this may be cause of the problem (???)

**excelnabb** · 01-01-2018, 01:45 PM

Great, I get most of the formulas to work now!

There are only two formulas I can't get to work. The minimum price for the last hour with time and volume (R7, S7,T7), and the volume trend arrow in u7 and AF7.
For the minimum price for the last hour, it seems that it needs to be shifted one cell up, as it doesn't take into account the cell at 15.00 (it starts to count from 15.01), and it takes into account the first cell the next day, 09.31)
takes 1 trade of day 3 in to account.JPG.

**JohnTopley** · 01-01-2018, 02:27 PM

in R7

=MIN(OFFSET(Data!$D$8,Helper!$C$9-1,,Helper!$C$8-Helper!$C$9+1))

in S7

=INDEX(OFFSET(Data!$G$8,Helper!$C$9-1,,Helper!$C$8-Helper!$C$9+1),MATCH($R$7,OFFSET(Data!$D$8,Helper!$C$9-1,,Helper!$C$8-Helper!$C$9+1),0))

in T7

=INDEX(OFFSET(Data!$F$8,Helper!$C$9-1,,Helper!$C$8-Helper!$C$9+1),MATCH($R$7,OFFSET(Data!$D$8,Helper!$C$9-1,,Helper!$C$8-Helper!$C$9+1),0))

I'll leave the mathematicians to resolve U7 and AF7

**excelnabb** · 01-01-2018, 03:26 PM

Thank you!
Though it seems like the formula in R7,S7 and T7, is depended on that there is a recorded price at 15:00. Not all stocks have recordings for every minute as mention, some have recordings only for every 5minutes, or have no action for a lot of several minutes. If you could make the helper time thing in B8 and B9 so they are not depended on the time between 15 and 16, but instead look up for the last hour of the day (between a2 and a3), then I don't think we will get the N/A if the marked close earlier one day, or some timestamps are missing

Regarding U7 and AF7: the formula which is there, is suppose to be correct, it "just" needs to be combined with the last hour/and first hour formula to work, I'm not sure how easy that is though...

**JohnTopley** · 01-01-2018, 03:37 PM

They do look for the last hour:

**excelnabb** · 01-01-2018, 04:07 PM

hmm...Okay. Don't know why I got #N/A on the ASTC stock. I can't find a similar stock atm with missing numbers to replicate the problem, but I'm guess its all okay then

I think I got this formula to work on the trend in the U cell:
=IF(LINEST((OFFSET(Data!$F$8,Helper!$C$9-1,,Helper!$C$8-Helper!$C$9+1)))>0,"↑","↓")

In AF I have no clue, as AC (where it finds the highest price within the first hour) use a differen formula then finding the lowest price within the last hour.

Thanks a lot for the help though!

Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Re: Index, match formula that combines Vlook up formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula needed for payroll worksheet calculation - index/match, vlook, other?

[SOLVED] Vlook up or index match

Bookmarks

Bookmarks

Posting Permissions