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

 

[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!

 

[thankyou]

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.

 

[Peter_SSs]

Glad you ended up with something you are happy with. Thanks for letting us know.

 

[thankyou]

Glad you ended up with something you are happy with. Thanks for letting us know.

Thanks Peter. Keep kicking a$$ and taking values. All the best.

 

[ewok2000]

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:

5	1
3	2
2	3
0	3
5	4
0	4
-4	-1
-2	-2
0	-2
-5	-3
2	1

<colgroup><col style="width:60pt" span="2" width="80"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

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:

5	1
3	2
2	3
0	3
5	4
0	4
-4	-1
-2	-2
0	-2
-5	-3
2	1

<tbody>
</tbody>

Would you "repeat" the objective?

 

[ewok2000]

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

	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

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

 

[Aladin Akyurek]

@ewok2000

I wasn't asking for formulas (even if they would be appropriate for your problem)...

 

[ewok2000]

so I dont understand your question. What do you need?

 

[Aladin Akyurek]

so I dont understand your question. What do you need?

Row\Col	A​	B​
2​	5​	1​
3​	3​	2​
4​	2​	3​
5​	0​	3​
6​	5​	4​
7​	0​	4​
8​	-4​	-1​
9​	-2​	-2​
10​	0​	-2​
11​	-5​	-3​
12​	2​	1​

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