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

mikej60

New Member
Joined
Oct 7, 2019
Messages
6
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.

3/11/197,558.06
3/8/197,408.14
3/7/197,421.46132
3/6/197,505.92

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,507
Office Version
365
Platform
Windows
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?
 
Last edited:

mikej60

New Member
Joined
Oct 7, 2019
Messages
6
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

11-Jan-19
6,971.48-14.59
10-Jan-19
6,986.0728.99
9-Jan-19
6,957.0860.08
8-Jan-19
6,897.0073.53
7-Jan-19
6,823.4784.61
4-Jan-19
6,738.86275.36
3-Jan-19
6,463.50
-202.44275.36
2-Jan-19
6,665.9430.66
31-Dec-18
6,635.2850.76
28-Dec-18
6,584.525.03
27-Dec-186,579.4925.13
26-Dec-186,554.36361.44
24-Dec-186,192.92-140.07361.44
21-Dec-186,332.99-195.42-140.072
20-Dec-186,528.41-108.42-195.423
19-Dec-186,636.83
-147.08-108.424
18-Dec-186,783.9130.18
17-Dec-186,753.73-156.9330.18
14-Dec-18
6,910.66
-159.67-156.9316
13-Dec-18
7,070.33-27.98
12-Dec-187,098.3166.48

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

New Member
Joined
Oct 7, 2019
Messages
6
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

New Member
Joined
Oct 7, 2019
Messages
6
AND realized theres a mistake - 19-dec-18 should be 8 in col5 not 4 - this is why I need a formula! ;)
 

mikej60

New Member
Joined
Oct 7, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,507
Office Version
365
Platform
Windows
This is how I would approach it. See what you think.

Excel Workbook
ABCD
1DatePrice>80 DropRecovery Days
211-Jan-196,971.48
310-Jan-196,986.07
49-Jan-196,957.08
58-Jan-196,897.00
67-Jan-196,823.47
74-Jan-196,738.86
83-Jan-196,463.50-202.44
92-Jan-196,665.94
1031-Dec-186,635.28
1128-Dec-186,584.52
1227-Dec-186,579.49
1326-Dec-186,554.36
1424-Dec-186,192.92-140.07
1521-Dec-186,332.99-195.422
1620-Dec-186,528.41-108.423
1719-Dec-186,636.83-147.088
1818-Dec-186,783.91
1917-Dec-186,753.73-156.93
2014-Dec-186,910.66-159.6716
2113-Dec-187,070.33
2212-Dec-187,098.31
Sheet1
 

mikej60

New Member
Joined
Oct 7, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,507
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,089,546
Messages
5,408,876
Members
403,238
Latest member
George_Ong

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top