Formula for Sum of Max Consecutive Negative Values D5:D29

T

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
 
Sort by date Sort by votes
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
ewok2000 said:
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]
Click to expand...

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
ewok2000 said:
so I dont understand your question. What do you need?
Click to expand...

[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
You must log in or register to reply here.

Similar threads

A
  • Question Question
formula help please: to return MAX number of consecutive 1's in column when the table filtered
Replies
4
Views
869
DanteAmor
DanteAmor
M
  • Solved
Count consecutive number with largest value and sum this
2
Replies
16
Views
2K
meohen1992
M
T
  • Question Question
Sum consecutive losses and equity Drawdown
Replies
1
Views
385
Trie1977
T
J
  • Question Question
How to: sum if only the most recent consecutive numbers in cells of same column appear.
Replies
5
Views
844
Josean
J
nostradamus
  • Question Question
Conditional data selection and calculate count, max and means
Replies
0
Views
412
nostradamus
nostradamus

Share this page

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