Counting the frequency of consecutive occurrences in a column?

[nickstefan12]

I have a column of numbers (I'll make up an example here).

Column G
2%
-4%
-3%
3%
-5%
-10%
-11%
-1%
-4%
-5%
1%
etc

I'm trying to devise two formulas:

1. Returns the maximum string of consecutive numbers smaller than -2%. In my example above: 3 (ie the string of -5%,-10%,-11%)

2. Returns the number of occurrences where at least 2 consecutive numbers smaller than -2% occur. In my example above: 3 (ie "-4% and -3%", "-5%, -10%, -11%", and "-4%, -5%").

For the first, I tried using combinations of the frequency and max formulas. For the second I tried using the sum and frequency formulas. Both instances aren't matching up with my "counting by hand". I've tried searching the internet, but I'm not having any luck...

THANK YOU SO MUCH!

 

[shg]

For the first, assuming the data starts in G2,

=MAX(FREQUENCY(IF(G2:G12<-2%, ROW(G2:G12)), IF(G2:G12>=-2%, ROW(G2:G12))))

... confirmed with Ctrl+Shift+Enter.

EDIT: For the second,

=SUMPRODUCT(--(FREQUENCY(IF(G2:G12<-2%, ROW(G2:G12)), IF(G2:G12>=-2%, ROW(G2:G12)))>=2))

Ditto for CSE.

 

[T. Valko]

I have a column of numbers (I'll make up an example here).

Column G
2%
-4%
-3%
3%
-5%
-10%
-11%
-1%
-4%
-5%
1%
etc

I'm trying to devise two formulas:

1. Returns the maximum string of consecutive numbers smaller than -2%. In my example above: 3 (ie the string of -5%,-10%,-11%)

2. Returns the number of occurrences where at least 2 consecutive numbers smaller than -2% occur. In my example above: 3 (ie "-4% and -3%", "-5%, -10%, -11%", and "-4%, -5%").

For the first, I tried using combinations of the frequency and max formulas. For the second I tried using the sum and frequency formulas. Both instances aren't matching up with my "counting by hand". I've tried searching the internet, but I'm not having any luck...

THANK YOU SO MUCH!

Try these array formulas**.

1.

=MAX(FREQUENCY(IF(G2:G12<-2%,ROW(G2:G12)),IF(G2:G12>=-2%,ROW(G2:G12))))

2.

=SUM(IF(FREQUENCY(IF(G2:G12<-2%,ROW(G2:G12)),IF(G2:G12>=-2%,ROW(G2:G12)))>=2,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[nickstefan12]

Thanks. Worked perfectly.

 

[T. Valko]

Thanks. Worked perfectly.

You're welcome. Thanks for the feedback!

 

[nickstefan12]

Hey I'm having some problems with this formula:

=MAX(FREQUENCY(IF(G:G>J4,ROW(G:G)),IF(G:G<=J4,ROW(G:G))))

It's returning 191 when G doesnt even have 191 entries! haha

Its supposed to calculate "the maximum string of consecutive numbers LARGER than J4". I have the formula working fine for the examples in this thread. I just can't quite tweak it to work with the parameter of "larger than".

THANK YOU

 

[Aladin Akyurek]

Hey I'm having some problems with this formula:

=MAX(FREQUENCY(IF(G:G>J4,ROW(G:G)),IF(G:G<=J4,ROW(G:G))))

It's returning 191 when G doesnt even have 191 entries! haha

Its supposed to calculate "the maximum string of consecutive numbers LARGER than J4". I have the formula working fine for the examples in this thread. I just can't quite tweak it to work with the parameter of "larger than".

THANK YOU

Try to use the current range as reference instead of whole columns... as in:

=MAX(FREQUENCY(IF(G2:G100>J4,ROW(G2:G100)),IF(G2:G100<=J4,ROW(G2:G100))))

 

[nickstefan12]

hmm. I tried referencing G2:G520 instead of G:G, It's still acting funny.

If I have a range like this:

G
-2%
4%
3%
-3%
5%
10%
11%
-1%
4%
5%
-1%

I'd like to devise two formulas:

1. Returns the maximum string of consecutive numbers LARGER than 1%. In my example above: 3 (ie the string of 5%,10%,11%)

2. Returns the number of occurrences where at least 2 consecutive numbers LARGER than 1% occur. In my example above: 3 (ie "4% and 3%", "5%, 10%, 11%", and "4%, 5%").

I tried messing with the formulas you guys gave me originally, but I can't get them to work...

 

[Aladin Akyurek]

hmm. I tried referencing G2:G520 instead of G:G, It's still acting funny.

If I have a range like this:

G
-2%
4%
3%
-3%
5%
10%
11%
-1%
4%
5%
-1%

I'd like to devise two formulas:

1. Returns the maximum string of consecutive numbers LARGER than 1%. In my example above: 3 (ie the string of 5%,10%,11%)

With J4 set to 1%, the following:

=MAX(FREQUENCY(IF(G2:G12>J4,ROW(G2:G12)),IF(G2:G12<=J4,ROW(G2:G12))))

confirmed with control+shift+enter, will yield 3 as result for the avove sample. Do you get a different result?

2. Returns the number of occurrences where at least 2 consecutive numbers LARGER than 1% occur. In my example above: 3 (ie "4% and 3%", "5%, 10%, 11%", and "4%, 5%").

In which way, This question is different from the first?

 

[nickstefan12]

With J4 set to 1%, the following:

=MAX(FREQUENCY(IF(G2:G12>J4,ROW(G2:G12)),IF(G2:G12<=J4,ROW(G2:G12))))

confirmed with control+shift+enter, will yield 3 as result for the avove sample. Do you get a different result?



In which way, This question is different from the first?

I have a number of sheets. Using G:G was a nice convenience that worked JUST fine with the OP! (why the !@#$ does it not work now? haha). I tried using some G2:G599 so that I could cut and paste it among all the sheets. Still some nonsensical answers. Now I'm adding custom range sizes, and... it works. Steve Ballmer, why does my G:G not work?