+ Reply to Thread
Results 1 to 9 of 9

Highest peak to Lowest trough

  1. #1
    Registered User
    Join Date
    03-31-2016
    Location
    New Delhi, India
    MS-Off Ver
    2010
    Posts
    3

    Question Highest peak to Lowest trough

    Hello,

    The data is series of positive and negative values in a column from which I need to extract the max drawdown as follows:

    Start summing up the values from occurrence of a negative value. The consecutive values are added into the sum until the sum turns positive. The last maximum negative value, before it turns positive, is the drawdown.
    After resetting the sum to zero, summing begins again when it finds the next negative number and the result becomes drawdown only if it is lesser than the last calculated drawdown.
    After going through the whole row, Max. Drawdown is obtained.

    I've tried a lot to find answer but available responses are to reset the operation when a positive value is encountered. I need to reset the value only if the sum itself becomes positive.

    Appreciate any help.
    Attached Files Attached Files
    Last edited by jonkaal; 03-31-2016 at 10:04 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Highest peak to Lowest trough

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-31-2016
    Location
    New Delhi, India
    MS-Off Ver
    2010
    Posts
    3

    Re: Highest peak to Lowest trough

    Thanks for a quick response, I've attached the sample file and edited a mistake i made. Hope it demonstrates the requirement in detail.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Highest peak to Lowest trough

    The easiest way for me was to use User Defined Function (UDF). See attachment. The code of the function is:
    Please Login or Register  to view this content.
    and you use it in a workbook as any standard function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As this is VBA code, macros have to be enabled after you open the workbook.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Highest peak to Lowest trough

    I got sort of the same result using this series of steps:

    1) Enter 0 in D2
    2) D3 =IF(B3<=0,D2+B3,0)
    3) copy D3 down to the bottom of the data set.
    4) F3 =MIN(D2:D400)

    I note that it does not place the "drawdown" sum in the same place. The -176.957 value is in row 144 instead of row 169, but it still seems to return the same values. I do not fully understand the significance of the "yellow" blocks that you have highlighted, so there may be something I am missing.
    Last edited by MrShorty; 03-31-2016 at 11:29 AM. Reason: Kaper caught error in D3 formula
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Highest peak to Lowest trough

    Hi,
    Either I somehow do not follow, or the layout of data is different, or something else went wrong (?)

    I think that min of a column
    where in row 2 is
    0
    and in next rows
    formula if cell in other column is negative, then increase previous value (going positive: 1, 2, ... etc.), if not, reset sequence again to 0
    will result in
    0
    (no cell in column D could be negative this way).

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Highest peak to Lowest trough

    @Kaper, I misentered my D3 formula -- thank you for catching my mistake. D3 should be =IF(B3<=0,D2+B3,0)

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Highest peak to Lowest trough

    Hi again,

    Hm, that sounds valid.
    What's worse - you got right result for test data.

    But try writing in B17 for instance -700
    The result shall be obviously -700, but it isn't.

    So my proposition of formula in D3 (and copy down) is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-31-2016
    Location
    New Delhi, India
    MS-Off Ver
    2010
    Posts
    3

    Re: Highest peak to Lowest trough

    Thanks a lot for the help.
    The files you uploaded seem to do the job. Though I'm inclined to use UDF, I'll test both methods on real data.
    I'll post if any doubts arise.

    Thanks a ton for help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  2. Replies: 6
    Last Post: 05-01-2015, 12:02 PM
  3. find peak and trough in data
    By qwertyjjj in forum Excel General
    Replies: 3
    Last Post: 04-23-2015, 04:01 PM
  4. Replies: 1
    Last Post: 12-17-2014, 11:28 PM
  5. peak to trough timeline
    By aldica in forum Excel General
    Replies: 1
    Last Post: 12-12-2014, 06:46 PM
  6. Finding Peak and Trough
    By VJR in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-01-2014, 11:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1