Formula for Sum of Max Consecutive Negative Values D5:D29

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
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
 
Peter, please ignore/delete my last post where I was asking for clarification on "G". It's obvious and needs no clarification. Sorry about that. I'm implementing it now and will follow up soon. Kind regards!
 
Upvote 0
SUCCESS! Very impressed with how this thread came together. And, of course, if I were to say "AUSum", well, I think all off you will get it. But I do think Microsoft should add it as one of their best formulas for success. Not only was everyone counted, but it was a cumulative effort at once! hmmm, now that's nerd for cool! My best regards. Thanks once again. Also, if I may, I'd like to give a "shoutout" to Repairman, who, while not in this thread, initially turned me onto mrexcel.com and made a great impression. Thanks again to all.
 
Upvote 0
Glad you ended up with something you are happy with. Thanks for letting us know. :)
 
Upvote 0
I am using Peter's formula but I have a special issue
In my lists there are also 0, which means the person did not participate and in your formula it counts the zeros for a win.
Do you think you can help me?

It should look like this:

[TABLE="width: 160"]
<colgroup><col style="width:60pt" span="2" width="80"> </colgroup><tbody>[TR]
[TD="width: 80, align: right"]5[/TD]
[TD="width: 80, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]-4[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]-5[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I am using Peter's formula but I have a special issue
In my lists there are also 0, which means the person did not participate and in your formula it counts the zeros for a win.
Do you think you can help me?

It should look like this:

[TABLE="width: 160"]
<tbody>[TR]
[TD="width: 80, align: right"]5[/TD]
[TD="width: 80, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]-4[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]-5[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Would you "repeat" the objective?
 
Upvote 0
I use this formula but I would need a solution when in the list is a 0 (for did not participate) that it reads the zero as that nothing happpened and not as a win.
it should look like in the thread above

If I have interpreted that correctly then try this.

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

Streaks 2

DEFGHI

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:50px;"><col style="width:35px;"><col style="width:35px;"><col style="width:55px;"><col style="width:55px;"><col style="width:61px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]value[/TD]

[TD="align: center"]count[/TD]
[TD="align: center"]sum[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]neg[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #99cc00, align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]pos[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #99cc00, align: center"]1[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #99cc00, align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #99cc00, align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-5[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #99cc00, align: center"]5[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #99cc00, align: center"]5[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #99cc00, align: center"]5[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #99cc00, align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #00ccff, align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="bgcolor: #00ccff, align: center"]20[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
E5=IF(D5="","",IF(D5<0,IF(D4<0,E4-1,-1),IF(D4<0,1,E4+1)))
F5=IF(SIGN(E5&0)=SIGN(E6&0),"",SUM(D$5:D5)-SUM(F$4:F4))
H5=-MIN(E5:E29)
I5=MIN(F5:F29)
H6=MAX(E5:E29)
I6=MAX(F5:F29)

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
so I dont understand your question. What do you need?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][/tr]
[tr][td]
2​
[/td][td]
5​
[/td][td]
1​
[/td][/tr]

[tr][td]
3​
[/td][td]
3​
[/td][td]
2​
[/td][/tr]

[tr][td]
4​
[/td][td]
2​
[/td][td]
3​
[/td][/tr]

[tr][td]
5​
[/td][td]
0​
[/td][td]
3​
[/td][/tr]

[tr][td]
6​
[/td][td]
5​
[/td][td]
4​
[/td][/tr]

[tr][td]
7​
[/td][td]
0​
[/td][td]
4​
[/td][/tr]

[tr][td]
8​
[/td][td]
-4​
[/td][td]
-1​
[/td][/tr]

[tr][td]
9​
[/td][td]
-2​
[/td][td]
-2​
[/td][/tr]

[tr][td]
10​
[/td][td]
0​
[/td][td]
-2​
[/td][/tr]

[tr][td]
11​
[/td][td]
-5​
[/td][td]
-3​
[/td][/tr]

[tr][td]
12​
[/td][td]
2​
[/td][td]
1​
[/td][/tr]
[/table]


What is given and what is output if any in the above exhibit?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top