# Number of days it took a stock to rise/sink above a certain level.

#### SKYTALKER

##### New Member
I tried to use multiple IF formulas, but it got too complicated for me and I also couldn't get them to work. I am using Excel 2019, 64-bit. I'm not positive that my explanation fully explains what I am trying to do, so it's probably better to look in file 1.

1. In 'FILE1', find if stock in A1 is found in column A in 'FILE2', if so:
2. In 'FILE1', find if the date in G1 is found in column G of 'FILE2', if so:
3. Add B1+1, then insert how many days for this condition to happen in column J.

4. H1<I1 - this determines whether to buy/sell (can H1>I1 be used in same formula?)

FILE 1 = This is a list of the top "movers" in the stock market for the day.
FILE 2 = This is a list of all the stocks sold on a particular day. This has several sheets, titled 11-09, 11-10, 11-11, etc., which are the list for each day.

Thanks for any help,
Cliff

P.S. I received this advice from another forum, but unfortunately, my lack of knowledge of Excel prevented me from using the info:

#1 : simple INDEX EQUIV combinaison. You could also merge your two sources in PowerQuery to get even faster computation results (especially if the list goes on).
#3 : IF(H2=MIN(H2:I2);"SELL";"BUY") should do the trick. Change H2:I2 to the proper value once your INDEX EQUIV works.
#4 : counting with multiple occurrences is very effective with COUNTIFS formula. When dealing with dates, remember to enter the condition like so : ">"&B5

The individual was from Paris, so I just assumed INDEX EQUIV was INDEX/MATCH.

#### Attachments

• 11 KB Views: 2
• 10.2 KB Views: 2

#### pecoflyer

##### Active Member
P.S. I received this advice from another forum, but unfortunately, my lack of knowledge of Excel prevented me from using the info

#### SKYTALKER

##### New Member
Sorry, I thought I was only supposed to write a reference to another forum, not add the actual link to the thread. Here it is:

#### SKYTALKER

##### New Member
I was able to find some earlier work that I had thought I had lost when my laptop crashed when I dropped it, but I was able to find it and rebuild it. Anyway, I hope someone can understand what I am trying to accomplish and can help.

#### Attachments

• 11.5 KB Views: 1

#### SKYTALKER

##### New Member
I have got part of my problem figured out using helper columns, but I am still having problems. I would still like to figure out a way to combine all formulas into 1, but I guess I will just have to take what I can get. The problem I am having is highlighted in yellow, such as K7, L7, etc.

The first problem is with the formula in K7: {=IF(\$H7>\$I7,INDEX(\$C8:\$C\$50,MATCH(TRUE,\$C8:\$C\$50<\$J7,0)),IF(\$H7<\$I7,INDEX(\$D8:\$D\$50,MATCH(TRUE,\$D8:\$D\$50>\$J7,0))))}
This formula is based on which cell is higher, H or I. If H is higher, it takes the value in J7 and looks down column C (LOW) and finds the next lower number. If I is higher, it takes the value in J7 and looks down column D (HIGH) and finds the next higher number.
I was trying to come up with a formula based on the following factors:
1 - What is in column A (SYM)
2 - Whether H or I is higher. This determines what goes in J7, which goes in K7, which determines the number we look up in column C or D, which determines what date goes in L7, which we will subtract from G7.
The problem is that at K7, we are looking for a value in column C, which is lower than J7 (115.40), but there aren't any lower, until we come to C18, which has a value of "AA" in column A.

I am trying to figure out a way to do the index/match that ONLY uses the value in column A as the limit which the formulas searches in column C or D, instead of 50 in \$C8:\$C\$50. I want it to stop at row 18 because "AA" is the value, instead of "A". Although I can manually change \$C8:\$C\$50 to \$C8:\$C\$17, I don't want to do that over 3000 times, which is constantly changing. If I can figure this out, I believe it will fix the rest of the errors.

Of course, if anyone can figure how to combine these formulas, I would be VERY happy, as I have been trying to figure this out for months! I have to use 3 helper columns to find each value that goes under the # of days, i.e., 1,2,3,etc. That means an extra 30 columns!

If ANYONE is willing to try to help but are confused about what I am trying to accomplish, please ask me and I will strive to explain better.

#### Attachments

• 30.4 KB Views: 1

#### SKYTALKER

##### New Member
Should anyone care, A very smart individual, BEBO021999, from Vietnam, was able to apparently solve this problem with the following formula:
=IFERROR(INDEX(\$G:\$G,AGGREGATE(15,6,ROW(\$A3:\$A\$13)/(\$A3:\$A\$13=\$A2)/(\$D3:\$D13>=\$B2+IF(\$I2>=\$H2,P\$1,-P\$1)),1))-\$G2,"")
I am checking it with my files to see if works correctly, but so far it has done everything I was asking for. This is from another forum at: https://www.excelforum.com/excel-formulas-and-functions/1331382-number-of-days-it-took-a-stock-to-rise-sink-above-a-certain-level.html#post5453827
Thanks to ANYONE who tried to work on a formula that did what I requested.

#### Attachments

• 14.5 KB Views: 2

#### SKYTALKER

##### New Member
I want to add something to the following formula:
=IFERROR(IF(\$H2<\$I2,INDEX('0MAS.xlsb'!DATE,MATCH(\$AA2,'0MAS.xlsb'!HIGH,0)),IF(\$H2>\$I2,INDEX('0MAS.xlsb'!DATE,MATCH(\$AA2,'0MAS.xlsb'!LOW,0)))),"")

The problem I am experiencing is that when it finds the value that MATCH is looking for, it finds the FIRST value, but there are other values that are the SAME number. I want to add a stipulation to H2<I2, which will differentiate it from the others, such as: ('0MAS=.xlsb'!SYM=A2)*(\$AA2,'0MAS=.xlsb'!HIGH).

OMAS= is the file with ALL of the stocks from every day, which is over 365K rows.
LOW = Column C.
HIGH = Column D.
SYM = Column A.
If ANYONE has ANY ideas, I sure would like to hear from you. Thanks in advance,
Cliff