Counting the number of rows until a threshold is exceeded in an adjacent row.

klemmeri

New Member
Joined
Nov 19, 2017
Messages
1
Hi - I'd like to find a formula for column D in this spreadsheet. It uses the adjacent value in column C and then looks in column B to find a 'day' (row) where that value is exceeded. The returned value in column D is the number of days it took to reach the 5% over price value. For example, the 'D' column is the number of days it took for the price to exceed 5% of $99.74 (i.e. >$104.73). I used countif (which doesn’t work since it looks through the entire column). Can anyone help here? Thanks – klemmeri

PS new to this forum, so apologize in advance for any missteps.

A B C D
Date Stock price 5% daily return 'Number of days (in the future) until > 5% reached'
1/3 99.74 104.73
1/4 98.74 103.70
1/5 99.90 104.90
1/6 96.58 101.41
. . .
. . .
. . .
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to Mr Excel forum

Maybe something like this

A
B
C
D
1
Date​
Price​
1.05*Price​
Days​
2
1/3​
99,74​
104,727​
4​
3
1/4​
98,74​
103,677​
3​
4
1/5​
99,9​
104,895​
Not found​
5
1/6​
96,58​
101,409​
1​
6
1/7​
104,74​
109,977​
Not found​

Formula in D2 copied down
=IFERROR(MATCH(1,INDEX(--(B3:B$1000>C2),),0),"Not found")

Hope this helps

M.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top