Count maximum frequency

Question

I am looking to count maximum frequency that occur in an array of data.  An individual piece of data in the array can have a value 0,1,2.  I would like it to count the frequency that 0 occurs in consistent weeks. 

For Example

1/1/2013	1/8/2013	1/15/2013	1/22/2013	1/29/2013	2/5/2013	2/12/2013	2/19/2013	2/26/2013	3/5/2013	3/12/2013	3/19/2013	3/26/2013	Count 0s
1	1	2	2	2	1	1	1	1	1	1	1	1	0
1	0	0	1	1	1	1	0	0	0	0	0	0	6
1	1	2	2	2	1	1	1	1	1	1	0	0	2
1	2	0	0	0	0	0	1	1	1	1	0	0	5

Row 1 is the week ending date

Row 2-5 is seperate arrays

The last column is the counts

So for example under Row 3 there are two weeks of 0 then there are 4 weeks of 1 and then there are 6 weeks of 0.  So the maximum continual weeks of 0 is 6.

Any help is appreciated.

Answer 1

Hi,

Try this ARRAY formula and see below on how to enter it. ARRAY enter and drag down

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

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.

Answer 2

Hi,

You may refer to my solution at the following link - http://www.ashishmathur.com/determine-the-maximum-number-of-consecutive-1s-appearing-in-a-range/

Hope this helps.