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

 

[Domenic]

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.

Yes, that's right, no VBA...

 

[Marcelo Branco]

Another possible solution without VBA

Same formulas as Aladin in C2 and C3

D2
=MIN((COUNTIF(OFFSET(A2:A14,ROW(A2:A14)-ROW(A2),0,C2),"<0")=C2)*SUBTOTAL(9,OFFSET($A$2:$A$14,ROW(A2:A14)-ROW($A$2),0,C2)))
Ctrl+Shift+Enter

D3
=MAX((COUNTIF(OFFSET(A2:A14,ROW(A2:A14)-ROW(A2),0,C3),">=0")=C3)*SUBTOTAL(9,OFFSET($A$2:$A$14,ROW(A2:A14)-ROW($A$2),0,C3)))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Remark

I edited the ranges in the first formula above
I was making tests with data in A2:A15.
But accordingly with data sample the proper range is A2:A14

M.

 

[martindwilson]

<text>deleted



</text>

 

[martindwilson]

oh grief wrong page again i'm of to bed

 

[thankyou]

Thanks for the excellent additions. Quite awesome. In addition to Aladin, I esp appreciate the additions from Domenic and Marcelo, and will be testing/trying these out Mon/Tue and post my follow up thereafter. With great appreciation and respect.

 

[Peter_SSs]

Deleted: rethinking

 

[Peter_SSs]

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

=SUM (etc)

Then what about some helper columns to keep the formulas somewhat simpler?

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

If I have interpreted that correctly then try this.

E4 remains empty.
E5 & F5 copied down to row 29.

Excel Workbook
	D	E	F	G	H	I
4	value				count	sum
5	-2	-1	-2	neg	1	-5
6	1	1		pos	4	22
7	1	2
8	1	3
9	1	4	4
10	-5	-1	-5
11	5	1
12	5	2
13	5	3
14	5	4	20
15	-2	-1	-2
16	2	1
17	20	2	22
18	-2	-1	-2
19
Streaks 2

 

[thankyou]

Then what about some helper columns to keep the formulas somewhat simpler?

Brilliant Peter! That style would work really well with mine since I tend to use a lot of helper columns & cells too. I can't wait to try this later today and followup thereafter. With kind regards for your efforts and attention to details. And of course I must share that sentiment with A, D & M as well naturally, or should I say natively, ha Have a great day.

 

[thankyou]

P.S. (added a couple mins later): Peter, I just noticed column "G" and was curious if or how it ties in sir. It may just have been one of your own helper columns as you were working on this solution. Thanks for your clarification, if you would. Will be following up later today, with appreciation always.