Annual Max Drawdown

[MarkLSC]

I was wondering if it was possible to modify the formula authored by Bernie Deitrick, in the formulas and functions post/file below, to account for the year column.

"479044d1473187645-drawdown-calculate-size-and-length-of-multiple-drawdown-phases-non-vba-draw-size-and-length-20160906.xlsx"

I'd like the formula in column C to start over whenever a new year (column A) starts in order to calculate the max drawdown in each year.

Any thoughts are greatly appreciated,

Cheers

PS - I would attach my own spreadsheet, but the paperclip function does not work for me in Chrome nor in Explorer so I referenced the original post and its attachment

[FlameRetired]

The paperclip function does not work for anyone ... hasn't for some time. We don't know why.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[gak67]

Without seeing your data then it's impossible to say, but something like this in C2 and dragged down should work:

Please Login or Register  to view this content.

Just replace the brackets ([]) with the relevant formulas.

[MarkLSC]

Great, thanks for the help. Let me see if this upload works.

The first equation provided in column C provides the drawdown % using the entire data series. I would like this same calculation for each year.

My attempt to solve the problem is in column G. I think it works, but duplicating this formula for several series seems to require alot of calculations to the point that Excel stops responding.

Is there any way to rewrite Column G more efficiently or attack the problem differently?

Thanks in advance for any suggestions

Cheers

[JeteMc]

See if this helps:
1) Convert the range A4:G1263 into a table,
2) Instead of the array entered formula with the whole column reference, that was in column F use:

Please Login or Register  to view this content.

Let us know if you have any questions.

[MarkLSC]

This is a very interesting way to tackle this. I have never created named tables and used this aggregate function. Something for my toolbox. Thanks so much JeteMc!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down above your first post. I hope that you have a blessed day.