Hi!
I need help in writing a formula to fetch the correct results. I have tried vlookup, match, index and few other syntax but unable to achieve what I want to. Appreciate some help from you guys. My problem is described below. Thanks for looking into it.
The region highlighted in yellow is the place where I need formulas that will auto-populate data from the raw data given in row 5:7
Let me explain:
F2 = In this cell, I need the close of date mentioned in cell A2 for the symbol "Temp" (B2)
Subsequent columns from G2:K2 = Here I need the high value of next 5 available dates in the raw data from the date mentioned in cell A2 for the symbol "Temp" (B2). Keep cell blank if enough data is not available. Eg. if the date in A2 is 18-Feb, then only 4 future dates are available in the raw data, in such a case, K2 will be blank.
L2 = In this cell I need to put after how many days, the value reached greater than or equal to 5% compared to value in cell F2. In this example, on the third day the value jumped to 9% and hence the number 3. Show zero if the value does not cross 5%
I am attaching the worksheet. Kindly assist me on this.
sheetscreenshot.png
Thank you
Bookmarks