consecutive positive numbers

[jmoore1197]

ok, i have in column A
1
2
1
4
-1
-9
1
2
-7
already i have it to where it will return: max consecutive positive numbers :4
max consecutive negative numbers : 2
what i want it to return now is the value of the max consecutive positive numbers: 1+2+1+4 = 8 I want it to return 8
and for max consecutive negative numbers would equal -1+-9 = -10 so it would return -10
i will have hundreds of numbers in this column so i want it to be able to do this for me without having to manuall go in there and find these values
PLEASE HELP
AND THANKS A TON!!!!

how do i do this?????

[zbor]

how did you get this:
max consecutive negative numbers : 2

[jmoore1197]

how did you get this:
max consecutive negative numbers : 2

max consecutive negative number :2 because i have 2 negative numbers in a row.
yes i know the max consec neg numbers is 2, now i want to know the value of these numbers added together. this would equal -10. see what im getting at?

[NBVC]

Can you show the formulas you have so far to give the max consecutive pos/neg numbers.

[jmoore1197]

ok what i did. i have these values in column A
1,2,1,-9,-5,-6,1,1....

for positive values:
so then in b1 i have if(a1>0,1,0) then in b2 i have if(a2>0,b1+1,0)

for negative values:
in c1 same as b1 but < and c2 same as b2 but <

then for max consecutive positives : =max(b:b)
and max consecutive negative: =max(c:c)

[jmoore1197]

i am a day trader. so what im doing is seeing how many positive trades ive had in a row and how much money it was and how many trades ive lost in a row and how much that was.

what is the most amount of positive trades in a row and what is that amount
what is the most amount of negative trades in a row and what is that amount

[zbor]

Like this?
=SUMIF(H25:L25;">0";H25:L25)
=COUNTIF(H25:L25;">0")

(opposite < for negative)

[jmoore1197]

not sure how to get that to work.

[zbor]

Just in some cell copy formula (probably you'll need to change ; into , )

and instead of ranges in formula select ranges with your data.

[NBVC]

Try:

Please Login or Register  to view this content.

for positives

and

Please Login or Register  to view this content.

for negatives

[jmoore1197]

still can't get it to work, if you don't mind, ill upload a sample and tell me what to do give me 5 min

[NBVC]

still can't get it to work, if you don't mind, ill upload a sample and tell me what to do give me 5 min

See my last post... Sumif() itself won't do what you want... I don't think.

Here's an attachment

[jmoore1197]

ok, here is a sample, maybe you can see what im talking about now

[jmoore1197]

try:

Please Login or Register  to view this content.

for positives

and

Please Login or Register  to view this content.

for negatives

perfect thanks!!!!!!!!!!!!!!!!!!!

[Ron Coderre]

With
A2:A10 containing groups of positive an negative numbers.

Please Login or Register  to view this content.

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, (instead of just
ENTER) returns the max count of consecutive postive values:

Please Login or Register  to view this content.

And this ARRAY FORMULA returns the sum of items in that group:

Please Login or Register  to view this content.

In the above example, the Max Group is: 3 (the 5,9,8 group)
and the sum of that group is: 22

Adjust range references to suit your situation.

Is that something you can work with?

[jmoore1197]

one more thing............

now what if i have many instances where 3 is the max amount of positives in a row. i want it to return the value of the highest 3

so if i have 1,1,1,-1,2,2,2

it will think i have max consecutive positives is 3 for a total of 3. but i want it to say total of 3 for a value of 6

[jmoore1197]

With
A2:A10 containing groups of positive an negative numbers.

Please Login or Register  to view this content.

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, (instead of just
ENTER) returns the max count of consecutive postive values:

Please Login or Register  to view this content.

And this ARRAY FORMULA returns the sum of items in that group:

Please Login or Register  to view this content.

In the above example, the Max Group is: 3 (the 5,9,8 group)
and the sum of that group is: 22

Adjust range references to suit your situation.

Is that something you can work with?

yes that is fine. but now see above post

[jmoore1197]

=SUM(OFFSET(A1,SMALL(IF(A2:A100<=0,ROW(A2:A100)),MATCH(B1,FREQUENCY(
IF(A2:A100>0,ROW(A2:A100)),IF(A2:A100<=0,ROW(A2:A100))),0))-B1-1,,B1))

i can't get this to work

[tawfiq]

sorry for interrupting you ... but i think that you have a problem in this formula in the case you will see it in the file below... please advise.

[jmoore1197]

i know, that is exactly what im saying. it works fine as long as there are not 2 cases where you have the same amount of consecutive positive values. then it only adds the first group. You are exactly right

[NBVC]

One way, since you are already using helper columns is to use a couple more helper columns..

so in D1:

=IF(B1=MAX(B:B),SUM(INDEX($A:$A,ROW()-MAX(B:B)+1):$A1),0)

copied to E1 and then copied down

Now to get max positive: =MAX(D:D)

To get smallest negative =MIN(D:D)

[jmoore1197]

One way, since you are already using helper columns is to use a couple more helper columns..

so in D1:

=IF(B1=MAX(B:B),SUM(INDEX($A:$A,ROW()-MAX(B:B)+1):$A1),0)

copied to E1 and then copied down

Now to get max positive: =MAX(D:D)

To get smallest negative =MIN(D:D)

Thanks will try when I get home. In transit

[jmoore1197]

One way, since you are already using helper columns is to use a couple more helper columns..

so in D1:

=IF(B1=MAX(B:B),SUM(INDEX($A:$A,ROW()-MAX(B:B)+1):$A1),0)

copied to E1 and then copied down

Now to get max positive: =MAX(D:D)

To get smallest negative =MIN(D:D)

Works Perfectly thanks for all you and the other guys help!!! i will be sure to return the favor if i can!!!!!!!

[NBVC]

Great! You're Welcome.

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[sherlucky]

=MAX(FREQUENCY(IF(A2:A29>0,ROW(A2:A29)),IF(A2:A29<=0,ROW(A2:A29))

I'm also trying to do what the day trader was doing but trying to find the maximum number or my winning trades for a given year. However, the formula keeps outputting 1. This is my data...

2.85%

2.79%

10.02%

3.67%

-2.53%

6.96%

2.54%

2.73%

5.19%

6.61%

-1.03%

-1.04%

-0.84%

3.81%

I think this is happening because there is a blank inbetween those numbers. Is there anyway to incorporate the blanks so I can get an answer of 5, since there are 5 consecutive positive trades.

[zbor]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.