How to find the next 0 in a range, after a specific date. What's the best way to do that?

[ExcelOnTheClock]

In investment management, there's a metric called max drawdown, which is the deepest that an asset price has fallen. I know how to calculate that.

I want to return the date when the asset price has returned back to its previous high, after its dropped to its current low. What's the best way to do that?

HERE is a link to the file. Explanations of the columns are below:

• Column C: monthly returns
• Column D: cumulative returns
• Column E: the highest cumulative return so far
• Column F: the deepest decline in returns so far
  • the deepest cumulative decline is 9/30/2013 at -19.53%

Now, I want to know when column F returns back to 0, after the date 9/30/2013. What's the best way to do that?

Thanks in advance for any help anyone can offer!

 

[Peter_SSs]

I want to know when column F returns back to 0, after the date 9/30/2013.

So why do you say the expected answer is 31/1/2015 when column F shows 0 before that on 31/12/2014?
.. and why do you mention cell B52?

 

[ExcelOnTheClock]

So why do you say the expected answer is 31/1/2015 when column F shows 0 before that on 31/12/2014?
.. and why do you mention cell B52?

Ah drat--I edited the file RIGHT before I uploaded. You're correct, I want to return Cell B47. I'm sorry for misspeaking. I want to return Dec 31, 2014 actually. I just fixed the file.

 

[Peter_SSs]

OK, then try this

=MINIFS(B4:B59,B4:B59,">"&I5,F4:F59,0)

 

[ExcelOnTheClock]

OK, then try this

=MINIFS(B4:B59,B4:B59,">"&I5,F4:F59,0)

That does it! I've never used MINIFS before, thanks for the help.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

BTW, For the future I suggest that you investigate XL2BB for providing sample data & expected results. It will generally get you faster/better responses as some helpers choose not to download actual files or are prevented from doing so by work-place restrictions.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[rowebca]

... or you can run a small loop:

Sub checkZero()
i = 4
Dim a As String
Do While i < ActiveSheet.Range("F7000").End(xlUp).Row
     If CDate(ActiveSheet.Cells(i, 2).Value) > CDate("9/30/2013") And ActiveSheet.Cells(i, 6) = 0 Then
       Exit Do
     End If
     i = i + 1
     Loop
MsgBox i
End Sub