Counting the frequency of consecutive occurrences in a column?

N

nickstefan12

New Member
Joined
May 28, 2011
Messages
5
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
nickstefan12 said:
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!
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
nickstefan12 said:
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
Click to expand...

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))))
 
Upvote 0
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...
 
Upvote 0
nickstefan12 said:
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%)
Click to expand...

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%").
Click to expand...

In which way, This question is different from the first?
 
Upvote 0
Aladin Akyurek said:
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?
Click to expand...

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? ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Consecutive formula, not counting most recent sequence
Replies
6
Views
189
jchick90
J
B
  • Question
Counting consecutive occurrences
Replies
4
Views
255
Peter_SSs
Peter_SSs
T
  • Question
Counting Designated Repeat Occurrences Over Set Threshold
Replies
0
Views
262
TrunkStore
T
M
  • Question
Excel Consecutive Formula
2
Replies
10
Views
332
mahmed1
M
S
  • Question
Multiple Conditions - Greater than less than
Replies
6
Views
348
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,054
Messages
6,122,893
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top