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

[klemmeri]

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
. . .
. . .
. . .

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Date​

[/td][td]

Price​

[/td][td]

1.05*Price​

[/td][td]

Days​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1/3​

[/td][td]

99,74​

[/td][td]

104,727​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1/4​

[/td][td]

98,74​

[/td][td]

103,677​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1/5​

[/td][td]

99,9​

[/td][td]

104,895​

[/td][td]

Not found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1/6​

[/td][td]

96,58​

[/td][td]

101,409​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

1/7​

[/td][td]

104,74​

[/td][td]

109,977​

[/td][td]

Not found​

[/td][/tr]
[/table]

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

Hope this helps

M.