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!

 

[Aladin Akyurek]

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?

1) Whole column references are expensive (and not possible on versions prior to 2007). Therefore less desirable.

2) The following would give a wrong result:

G1: Data

which is just a header.

G2: 2%
G3: 5%
G4: 0.7%
G5: 0.4%

Given just what is in above:

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

with J4 = 1% will yield an erroneous result for text values are > numeric values.

3) Try to convert the data area into a table like below...

<table width="64" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl63" style="height:14.4pt;width:48pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" id="td_post_2734593" width="64" height="19">Item</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="19"> -2%

</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext" align="right" height="19">4%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="19">3%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext" align="right" height="19">-3%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="19">5%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext" align="right" height="19">10%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="19">11%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext" align="right" height="19">-1%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="19">4%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl64" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext" align="right" height="19">5%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl65" style="height:14.4pt;border-top:none; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Verdana;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="19">-1% </td> </tr> </tbody></table>
The formula becomes:

{=MAX(FREQUENCY(IF(Table1[Item]>J4,ROW(Table1[Item])),IF(Table1[Item]<=J4,ROW(Table1[Item]))))}

This could be easier to manage per sheet.

4) You could also devise a dynamic named range per sheet like in the example that follows...

JanList

defined as referring to:

=Jan!$G$2:INDEX(Jan!$G:$G,MATCH(BigNum,Jan!$G:$G))

where BigNum is also a definition that refers to

=9.99999999999999E+307

FebList would be:

=Feb!$G$2:INDEX(Feb!$G:$G,MATCH(BigNum,Feb!$G:$G))

The formula of interest can be re-expressed then as:

{=MAX(FREQUENCY(IF(JanList>J4,ROW(JanList)),IF(JanList<=J4,ROW(JanList))))}

{=MAX(FREQUENCY(IF(FebList>J4,ROW(FebList)),IF(FebList<=J4,ROW(FebList))))}

And so on.

Hope this helps.

 

[T. Valko]

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

Don't use entire coulumns as range references. Use a specific sized range.

Using the entire column is "quick and easy" but it's also a waste of resources if you're not actually using every single cell in the column.

An array formula will evaluate every cell referenced.

If need be, you could use dynamic ranges that automatically adjust in size when data is entered or removed in a range.

Post back if you want to pursue that.