Ask a new question

count a consecutive series or positive or negative numbers and then get a sum of the max frequency

Example

-1

-2

-3

1

3

5

7

0

-1

 

max positive count is 4 and the sum is 16.

max negative count is 3 and the sum is -6

Answer
Answer

Hi,

 

For these formula to work the column of numbers; I used column A, must have a header that is NOT part of the number sequence. All the following formula are ARRAY formula, see below on how to enter them

 

1. Maximum run of positives which is in cell H1

 

=MAX(FREQUENCY(IF(A1:A20>0,ROW(A1:A20)),IF(A1:A20<=0,ROW(A1:A20))))

 

2. Sum of consecutive positives and note this references the formula above in H1

 

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

 

3. Maximum run of negatives which is in cell H4

 

=MAX(FREQUENCY(IF(A1:A20<0,ROW(A1:A20)),IF(A1:A20>=0,ROW(A1:A20))))

 

4. Sum of consecutive negatives and note this references the formula above in H4

 

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

 

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

EDIT. If you want the SUM formula not to be dependant on the other cells it gets quite long but this is them

 

Positives

 

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

 

negatives

 

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

 

Both still ARRAY formula

If this response answers your question then please mark as answer.

Mike H

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 18, 2024 Views 2,243 Applies to: