how to report number of cells in a colunm (searching up) between a number and the first instance of a larger number?

[mikej60]

Probably pretty basic question - only starting t play with excel - simple data set - daily nasdaq prices in descending data order; looking to report the number of days it takes to get back to equal or greater price after two consecutive drops - so for 3/8, second drop was 13 points - want to put function in next column by counting number of cells up from 7421.46 to the next occurrence of a value greater than it - i.e. 7558 answer is 2. Having a hard time conceptualizing how to approach this. any help appreciated.

[TABLE="width: 280"]
<tbody>[TR]
[TD="align: right"]3/11/19[/TD]
[TD="align: right"]7,558.06[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3/8/19[/TD]
[TD="align: right"]7,408.14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3/7/19[/TD]
[TD="align: right"]7,421.46[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3/6/19[/TD]
[TD="align: right"]7,505.92[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Since the second drop (of 13 points) was on 3/8 why are you showing that value on the 3/7 line and counting the days from that line.

Also what would you want to happen if there were say 4 drops in a row & then some rises and more drops? So, could we have another sample or two, with results & explanation that are a little more complex?

 

[mikej60]

thanks for the response - guess I'm not figuring out how this particular board works too well as I couldnt see any response or even see that Ihad posted my question - found it by searching for nasdaq as a fairly unique word I knew I included! So I will try and clarify -
reason the 13 pt drop is shown on the previous line (for the first drop) is because my strategy would be to buy late in the day on the first drop (if it was big enough - here more than 80 pts) - though this doesnt really matter which line it is reported on, that should not affect the problem. this strategy assumes that after a big drop it is more likely to go up the next day and therefore make money. IF it goes down a second day, the formula needs to let me know how many days it then takes to get back to the starting price. Here's some more complex data - i've added a column showing the chage day to day for reference

[TABLE="width: 332"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"] 11-Jan-19
[/TD]
[TD="align: right"]6,971.48[/TD]
[TD="align: right"]-14.59[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 10-Jan-19
[/TD]
[TD="align: right"]6,986.07[/TD]
[TD="align: right"]28.99[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 9-Jan-19
[/TD]
[TD="align: right"]6,957.08[/TD]
[TD="align: right"]60.08[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 8-Jan-19
[/TD]
[TD="align: right"]6,897.00[/TD]
[TD="align: right"]73.53[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 7-Jan-19
[/TD]
[TD="align: right"]6,823.47[/TD]
[TD="align: right"]84.61[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 4-Jan-19
[/TD]
[TD="align: right"]6,738.86[/TD]
[TD="align: right"]275.36[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 3-Jan-19
[/TD]
[TD="align: right"]6,463.50
[/TD]
[TD="align: right"]-202.44[/TD]
[TD="align: right"]275.36[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 2-Jan-19
[/TD]
[TD="align: right"]6,665.94[/TD]
[TD="align: right"]30.66[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 31-Dec-18
[/TD]
[TD="align: right"]6,635.28[/TD]
[TD="align: right"]50.76
[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28-Dec-18
[/TD]
[TD="align: right"]6,584.52[/TD]
[TD="align: right"]5.03[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27-Dec-18[/TD]
[TD="align: right"]6,579.49[/TD]
[TD="align: right"]25.13[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26-Dec-18[/TD]
[TD="align: right"]6,554.36[/TD]
[TD="align: right"]361.44[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24-Dec-18[/TD]
[TD="align: right"]6,192.92[/TD]
[TD="align: right"]-140.07[/TD]
[TD="align: right"]361.44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21-Dec-18[/TD]
[TD="align: right"]6,332.99[/TD]
[TD="align: right"]-195.42[/TD]
[TD="align: right"]-140.07[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]20-Dec-18[/TD]
[TD="align: right"]6,528.41[/TD]
[TD="align: right"]-108.42[/TD]
[TD="align: right"]-195.42[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]19-Dec-18[/TD]
[TD="align: right"]6,636.83
[/TD]
[TD="align: right"]-147.08[/TD]
[TD="align: right"]-108.42[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]18-Dec-18[/TD]
[TD="align: right"]6,783.91[/TD]
[TD="align: right"]30.18[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Dec-18[/TD]
[TD="align: right"]6,753.73[/TD]
[TD="align: right"]-156.93[/TD]
[TD="align: right"]30.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14-Dec-18
[/TD]
[TD="align: right"]6,910.66
[/TD]
[TD="align: right"]-159.67[/TD]
[TD="align: right"]-156.93[/TD]
[TD="align: right"]16
[/TD]
[/TR]
[TR]
[TD="align: right"]13-Dec-18
[/TD]
[TD="align: right"]7,070.33[/TD]
[TD="align: right"]-27.98[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12-Dec-18[/TD]
[TD="align: right"]7,098.31[/TD]
[TD="align: right"]66.48[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

col2 shows daily change from day before; third column reports the price change ONLY the day AFTER a -80 pt drop the day before (i.e. when I might have bought); fourth column reports the number of days it takes for the price to recover - i.e. for 14 dec 18 it takes 16 days before the closing price is greater than 6910.66 . I want the formula to ask how many cells - trading or week days - are there between 6910.66 and the first occurrence of a larger number - searching up - in the second column? fourth column numbers manually calculated for now! 4th column reports if second drop occurs immediately after > -80 pt drop.

 

[mikej60]

oops my column designations are ignoring the actual date column - col3 shows daily change col4 price change after -80 drop the day before, col 5 the # (trading) days before the next higher price from the first > -80 drop. wanting excel to look up col2 to find the next larger number and report the difference in cell numbers or how many rows up it had to go to find the first occurrence of a higher number - hopefully I've made myself clearer!

 

[mikej60]

AND realized theres a mistake - 19-dec-18 should be 8 in col5 not 4 - this is why I need a formula!

 

[mikej60]

I think I have figured out how to do this using reverse lookup - but I dont understand exactly the syntax!
I added a counter column A starting at A2=1, incrementing by one as it fills down; in the formula below (refering to the above data) col4 is AB, col2 the daily price - is F, so the lookup function returns the counter column value for the next higher price, searching up (which is the bit I dont understand about the formula) which is subtracted from the counter column value for the daily price in question to give the number of trading days it takes to reach the next price higher than the given price, only reporting for cells that have a negative value in col4 in the above data.
=IF(AB227<0,A227-LOOKUP(2,1/($F$2:F227>F227),$A$2:A227),"")
QUESTION 1 What I'd like help with here is how the (2,1/ part of the lookup formula makes it search upwards in the range? I've fixed the top of the ranges so I can fill up the formula for the whole spreadsheet.
https://www.extendoffice.com/documents/excel/3966-excel-match-upwards.html "How to find matched value by searching upwards in Excel?" helped me figure out that how to use lookup "upwards" but doesnt explain what it does.
QUESTION 2 Also - is there a more elegant way to do this without adding in the counting column?

 

[Peter_SSs]

This is how I would approach it. See what you think.

Excel Workbook
	A	B	C	D
1	Date	Price	>80 Drop	Recovery Days
2	11-Jan-19	6,971.48
3	10-Jan-19	6,986.07
4	9-Jan-19	6,957.08
5	8-Jan-19	6,897.00
6	7-Jan-19	6,823.47
7	4-Jan-19	6,738.86
8	3-Jan-19	6,463.50	-202.44
9	2-Jan-19	6,665.94
10	31-Dec-18	6,635.28
11	28-Dec-18	6,584.52
12	27-Dec-18	6,579.49
13	26-Dec-18	6,554.36
14	24-Dec-18	6,192.92	-140.07
15	21-Dec-18	6,332.99	-195.42	2
16	20-Dec-18	6,528.41	-108.42	3
17	19-Dec-18	6,636.83	-147.08	8
18	18-Dec-18	6,783.91
19	17-Dec-18	6,753.73	-156.93
20	14-Dec-18	6,910.66	-159.67	16
21	13-Dec-18	7,070.33
22	12-Dec-18	7,098.31
Sheet1

 

[mikej60]

thank you - does exactly what I want but better! It took me a while to figure out what you had done but I think I got it - runs formula on cells only if neg value in C col, error check to report N/A if no larger price, not sure why IF checks for a price drop when value in C has already done that (maybe to stop reporting a number if price increases? - maybe without this it would return 1? that'd be OK too) subtract from current row number the largest row number where future price is higher than current price to get days to recover. Much more elegant - Excel can obviously do a lot more than I am used to using it for - but since I've never taken any classes in it, I have to figure out how to do things on an as needed basis! And I did find an explanation as to why lookup works with (2,1/formula. So the ROW function would replace my counting column in that case. Thanks again!

 

[Peter_SSs]

thank you - does exactly what I want but better!

You're welcome.


You were pretty much spot on with your assessment of the formula.
A few comments below:

..runs formula on cells only if neg value in C col Correct


, error check to report N/A if no larger price, Correct


not sure why IF checks for a price drop when value in C has already done that (maybe to stop reporting a number if price increases? maybe without this it would return 1? that'd be OK too) You are right - without the IF it would would return 1 (in D8, D14 & D19). However, you said you only wanted to do the calculation of recovery days after 2 consecutive drops & those rows do not meet that condition - & those rows in your expected results in post 3 are blank, hence the IF check.


subtract from current row number the largest row number where future price is higher than current price to get days to recover. Correct