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

 

[ewok2000]

First row its given Second should come with an answer

 

[Aladin Akyurek]

First row its given Second should come with an answer

You mean the second range is the answer, is it not?

 

[ewok2000]

but I need my colums which includes 0s.

I have peters forumula but this does not work with zeros (did not take part)

 

[Excel777]

dear sir,

your answer is the most accurate because sometimes max consecutive count doesn't match max consecutive sum, and this is the other answers don't take in considerations

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



If I have interpreted that correctly then try this.

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

Streaks 2

	D	E	F	G	H	I

<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

Cell Formula 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>

Excel tables to the web >> Excel Jeanie HTML 4