Number of days it took a stock to rise/sink above a certain level.
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,
P.S. I received this advice from another forum, but unfortunately, my lack of knowledge of Excel prevented me from using the info:
You've numbered your problems so I can provide a partial answer.
#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).
#2 : same answer.
#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
Help Article Resources
Check out the Formula Handbook template!