Formula for Sum of Max Consecutive Negative Values D5:D29

[thankyou]

Hi. I've done some research here but I'm afraid I haven't yet found the answer. I'm looking for a sum formula that will do these things:

1. SUM the largest streak of negative numbers in that range.(aka "max drawdown")
2. SUMthe largest streak of positive numbers in that range.(aka "max runup")

and then these 2 simple Counts:

1) # of consecutive cells bearing the longest streak of neg #s
2) # of consecutive cells bearing the longest streak of positive #s

No VBA or arrays if you don't mind. Just a formula like:

=SUM (etc)

I respect your time and experience.

thankyou

 

[Aladin Akyurek]

Try to provide a sample along with the expected results.

 

[Peter_SSs]

No VBA or arrays if you don't mind. Just a formula like:

=SUM (etc)

Interesting restriction.
If you have a simple problem like summing some values, expect to be able to use a simple formula like SUM()
If you have a complex problem, expect the solution may be more complex.

Surely you aren't saying that if it cannot be done without vba or array formula you don't want a solution?

1. SUM the largest streak of negative numbers in that range.(aka "max drawdown")

I'm assuming Aladin's request is aimed at least partly at determining whether the "largest streak" means the longest one or the one that sums to the most (or least for negatives).

a) If it means the longest one, what is to happen if there are multiple streaks of the same maximum length that have different sums?


b) If it means the one that adds to the most (or least), how would the second set of questions be answered if there were multiple streaks of different lengths that added to the same maximum value? Or perhaps the second set of questions is not related to the first?

 

[thankyou]

Aladin, Thank you for your post. Here's a typical example: 3 5 2 -1 -6 -2 0 3 9 8 4 -3 0 I'd appreciate excel formulas that will calculate the sum for the greatest run of + numbers (aka "runup") and neg numbers (aka "drawdown") In the above case, the formula for the greatest drawdown should catch the cells-1, -6, -2 and show "-9" in the cell For the runup version, another cell with a slightly different formula will show the sum of 0+3+9+8+4, or "24" thank you

 

[thankyou]

Thanks for your questions Peter.
YOU ASKED (between the asterixes): ***** 1. SUM the largest streak of negative numbers in that range.(aka "max drawdown") << I'm assuming Aladin's request is aimed at least partly at determining whether the "largest streak" means the longest one or the one that sums to the most (or least for negatives). a) If it means the longest one, what is to happen if there are multiple streaks of the same maximum length that have different sums?" *****

<<< ANSWER: Good question, and I just thought of that as I was preparing the example in response to Aladin, believe it or not. OK, my answer is that, since we're talking "max drawdown", then we surely need the largest sum of those multiple streaks that have the same length.

YOU ASKED (between the asterixes): ***** b) If it means the one that adds to the most (or least), how would the second set of questions be answered if there were multiple streaks of different lengths that added to the same maximum value? Or perhaps the second set of questions is not related to the first? *****

<<< ANSWER: You're referring to the counts I posted as follows: "and then these 2 simple Counts: 1) # of consecutive cells bearing the longest streak of neg #s 2) # of consecutive cells bearing the longest streak of positive #s" ...So, if you're asking "what do we do if there are MULTIPLE streaks of consecutive positive numbers with the SAME counts" (ie multiple longest streaks with the same counts), then, if we're looking for the "longest streak of positive (in example 1) or longest streak of negatives (example 2), then the answer is take either one as the count. It doesn't matter to me since they're just Counts (unlike the "Sums" in my answer to your first question above "a)").

 

[Aladin Akyurek]

Aladin, Thank you for your post. Here's a typical example: 3 5 2 -1 -6 -2 0 3 9 8 4 -3 0 I'd appreciate excel formulas that will calculate the sum for the greatest run of + numbers (aka "runup") and neg numbers (aka "drawdown") In the above case, the formula for the greatest drawdown should catch the cells-1, -6, -2 and show "-9" in the cell For the runup version, another cell with a slightly different formula will show the sum of 0+3+9+8+4, or "24" thank you

One way, with the following proviso's: (i) It requires an array formula which calls ACONCAT, a basic function coded in VBA; (ii) the first longest streak is summed [see Peter's point enumerated as (a) in his reply].

[TABLE="width: 127"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 33, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 41, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 31, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]count
[/TD]
[TD="class: xl65, bgcolor: transparent"]sum
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]neg
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-9
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]pos
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]-1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]-6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]-2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]-3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

C3, control+shift+enter (CSE), not just enter:

=MAX(FREQUENCY(IF(A2:A14 < 0,ROW(A2:A14)),
  IF(ISNUMBER(A2:A14),IF(A2:A14 >= 0,ROW(A2:A14)))))

C4, CSE:

=MAX(FREQUENCY(IF(ISNUMBER(A2:A14),
  IF(A2:A14>=0,ROW(A2:A14))),IF(A2:A14<0,ROW(A2:A14))))

The formulas for summing needs ACONCAT...

D3, CSE:

=SUM(OFFSET($A$2,LOOKUP(9.99999999999999E+307,
  FIND(aconcat(ROW(INDIRECT("1:"&C3))^0),
  aconcat(IF(A2:A14 < 0,SIGN(ROW(A2:A14)),0))))-1,0,C3))

D4, CSE:

=SUM(OFFSET($A$2,LOOKUP(9.99999999999999E+307,
  FIND(aconcat(ROW(INDIRECT("1:"&C4))^0),
  aconcat(IF(A2:A14>=0,SIGN(ROW(A2:A14)),0))))-1,0,C4))

You need to add the code for ACONCAT as module to your workbook:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[Peter_SSs]

Firstly, I have fixed your previous post. Don't use a "<" sign immediately followed by a letter as the forum software will interpret as html & cut off your post, as it had done.

In any case, an easier way to quote something from another post is to use the 'Reply with quote' button below a post and edit out what you don't want, or use [quote][/quote] tags around the text. There is a button above the Quick Reply window to do that if you don't want to type the tags manually.

example quote

Secondly, I'm not sue I will be able to help with your problem but I'm seeking further clarification - not required if Aladin's response has solved your problem.

Considering positive streaks only ...

In column A below the 2 green sections are the same length but clearly A7:A10 sums higher than A2:A5. A12:A13 sums higher again but is shorter. Can you confirm which range should be summed & counted?

Excel Workbook
	A
1	-2
2	1
3	1
4	1
5	1
6	-2
7	5
8	5
9	5
10	5
11	-2
12	2
13	20
14	-2
thankyou

 

[Domenic]

Here's a solution that is exactly the same as Aladin's solution for the first part, but uses native functions for the second part...

A1:D14

[TABLE="width: 192"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]count
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]sum
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent"]neg
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-9
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent"]pos
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]-1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]-6
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]-2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]-3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

C2, confirmed with CONTROL+SHIFT+ENTER:

=MAX(FREQUENCY(IF(A2:A14 < 0,ROW(A2:A14)),IF(ISNUMBER(A2:A14),IF(A2:A14 >= 0,ROW(A2:A14)))))

C3, confirmed with CONTROL+SHIFT+ENTER:

=MAX(FREQUENCY(IF(ISNUMBER(A2:A14),IF(A2:A14>=0,ROW(A2:A14))),IF(A2:A14<0,ROW(A2:A14))))

D2:

=INDEX(SUBTOTAL(9,OFFSET(A2:A14,ROW(INDIRECT("1:"&ROWS(A2:A14)-C2+1))-1,,C2)),MATCH(TRUE,INDEX(COUNTIF(OFFSET(A2:A14,ROW(INDIRECT("1:"&ROWS(A2:A14)-C2+1))-1,,C2),"<0")=C2,0),0))

D3:

=INDEX(SUBTOTAL(9,OFFSET(A2:A14,ROW(INDIRECT("1:"&ROWS(A2:A14)-C3+1))-1,,C3)),MATCH(TRUE,INDEX(COUNTIF(OFFSET(A2:A14,ROW(INDIRECT("1:"&ROWS(A2:A14)-C3+1))-1,,C3),">=0")=C3,0),0))

Hope this helps!

 

[thankyou]

Considering positive streaks only ... In column A below the 2 green sections are the same length but clearly A7:A10 sums higher than A2:A5. A12:A13 sums higher again but is shorter. Can you confirm which range should be summed & counted?

PS I've put the above paragraph in quotes using

and

. Peter (and Aladin of course), thanks for your above follow up question. It's a good one that we didn't get to before. The ANSWER to your question, Peter, is this: We have to get the basic concept of what we're asking here, which is the "Max Drawdown". That means, if you're talking about anything finance-related, it's the largest consecutive drop (ie before the next cell hits 0 or any positive number, which effectively resets the max drawdown). So, to use your example, Peter, the max "winning streak" (count of consecutive cells with values of zero or greater) would be "4", which is taken from either A2:5 or A7:10 in your example. For the 2nd component, you've asked Peter, namely, the "sum", that would be A12:13 or answer "22" in your example. Like you said, that's for positives only just to illustrate the point, with appreciation. That means I made a mistake in my original question slightly in "1" and "2" (sum-related). For those, they should read, instead: 1. SUM the largest cumulative LOSS (aka "Max Drawdown"). In your example again Pete, it would be "22" reflecting A12:13 of your example. Please do remember to make any code (like MVP Aladin's CONCAT-related stuff) to reflect my actual range of values which is D5:D29. Question for either of you please: If my cell range changes commonly (currently D5:D29), will I have to constantly manually update the CONCAT VBA code given the following: I usually paste new values into those cells (values only)...but I ALSO often have to insert or delete rows(before I paste the values) when there are more values than, say,25, since, as is common, there are other formulas on that sheet) that are dependent on one another. So, that question essentially is: "Will your CONCAT coding still work if I delete the original cells or rows?" (sorry if this sounds ridiculously stupid). 2. SUM the largest cumulative GAIN (aka "Max Runup"). The concept would be the same as in the previous paragraph but with negative values. Thanks for both of your help so far. It's valued and that value, indeed, is cumulative.

 

[thankyou]

Domenic, I see your post just came in and sorry I missed it when I posted just now above. I really appreciate your contributing here! Um, I have this question: does "native" mean no VBA, but we basically build on the awesome contribution from Aladin etc? Kind regards.