I want to count the highest number of equal cells
 

I got a worksheet with only 2 different numbers in each cell like this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...

I want to count the highest number of equal cells
 

=FREQUENCY(A1:A13,A1:A13)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fiur" wrote in message
...
I got a worksheet with only 2 different numbers in each cell like this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...

I want to count the highest number of equal cells
 

thanx alot for your tip.... but I need to be more spesific...... lets say I
got 4 cells in a row with 1 then two cells with 0 then I must start to count
over again....
I only wanna count the larges number of equal cells in a row (1-1-1-1-0-1-0-)
the result will be: number 1= 4
number 0= 1
Hopefully this will explain my problem better.....


"Bob Phillips" wrote:

=FREQUENCY(A1:A13,A1:A13)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fiur" wrote in message
...
I got a worksheet with only 2 different numbers in each cell like this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...

I want to count the highest number of equal cells
 

Use a helper column. Assume your numbers are in the range A1:A11 (assuming
no empty cells within the range).

Enter 1 in B1. Enter this formula in B2 and copy down to B11:

=IF(A2=A1,B1+1,1)

Then, to get the highest number of consecutive cells:

=MAX(B2:B11)

If you need to break it out by specific number, eg: max for 1 and max for 0:

=SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11))

=SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11))

Biff

"fiur" wrote in message
...
thanx alot for your tip.... but I need to be more spesific...... lets say
I
got 4 cells in a row with 1 then two cells with 0 then I must start to
count
over again....
I only wanna count the larges number of equal cells in a row
(1-1-1-1-0-1-0-)
the result will be: number 1= 4
number 0= 1
Hopefully this will explain my problem better.....


"Bob Phillips" wrote:

=FREQUENCY(A1:A13,A1:A13)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fiur" wrote in message
...
I got a worksheet with only 2 different numbers in each cell like this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...

I want to count the highest number of equal cells
 

thanx alot !!!!!!! that was exactly what I was looking for!!!!!
You're the man.....

"Biff" wrote:

Use a helper column. Assume your numbers are in the range A1:A11 (assuming
no empty cells within the range).

Enter 1 in B1. Enter this formula in B2 and copy down to B11:

=IF(A2=A1,B1+1,1)

Then, to get the highest number of consecutive cells:

=MAX(B2:B11)

If you need to break it out by specific number, eg: max for 1 and max for 0:

=SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11))

=SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11))

Biff

"fiur" wrote in message
...
thanx alot for your tip.... but I need to be more spesific...... lets say
I
got 4 cells in a row with 1 then two cells with 0 then I must start to
count
over again....
I only wanna count the larges number of equal cells in a row
(1-1-1-1-0-1-0-)
the result will be: number 1= 4
number 0= 1
Hopefully this will explain my problem better.....


"Bob Phillips" wrote:

=FREQUENCY(A1:A13,A1:A13)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fiur" wrote in message
...
I got a worksheet with only 2 different numbers in each cell like this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...

I want to count the highest number of equal cells
 

You're welcome. Thanks for the feedback!

Biff

"fiur" wrote in message
...
thanx alot !!!!!!! that was exactly what I was looking for!!!!!
You're the man.....

"Biff" wrote:

Use a helper column. Assume your numbers are in the range A1:A11
(assuming
no empty cells within the range).

Enter 1 in B1. Enter this formula in B2 and copy down to B11:

=IF(A2=A1,B1+1,1)

Then, to get the highest number of consecutive cells:

=MAX(B2:B11)

If you need to break it out by specific number, eg: max for 1 and max for
0:

=SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11))

=SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11))

Biff

"fiur" wrote in message
...
thanx alot for your tip.... but I need to be more spesific...... lets
say
I
got 4 cells in a row with 1 then two cells with 0 then I must start to
count
over again....
I only wanna count the larges number of equal cells in a row
(1-1-1-1-0-1-0-)
the result will be: number 1= 4
number 0= 1
Hopefully this will explain my problem better.....


"Bob Phillips" wrote:

=FREQUENCY(A1:A13,A1:A13)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fiur" wrote in message
...
I got a worksheet with only 2 different numbers in each cell like
this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...

I want to count the highest number of equal cells
 

An array formula without helper cells is more difficult to "maintain", but it
can be done in this case...

--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"fiur" wrote in message
...
thanx alot !!!!!!! that was exactly what I was looking for!!!!!
You're the man.....

"Biff" wrote:

Use a helper column. Assume your numbers are in the range A1:A11
(assuming
no empty cells within the range).

Enter 1 in B1. Enter this formula in B2 and copy down to B11:

=IF(A2=A1,B1+1,1)

Then, to get the highest number of consecutive cells:

=MAX(B2:B11)

If you need to break it out by specific number, eg: max for 1 and max for
0:

=SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11))

=SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11))

Biff

"fiur" wrote in message
...
thanx alot for your tip.... but I need to be more spesific...... lets
say
I
got 4 cells in a row with 1 then two cells with 0 then I must start to
count
over again....
I only wanna count the larges number of equal cells in a row
(1-1-1-1-0-1-0-)
the result will be: number 1= 4
number 0= 1
Hopefully this will explain my problem better.....


"Bob Phillips" wrote:

=FREQUENCY(A1:A13,A1:A13)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fiur" wrote in message
...
I got a worksheet with only 2 different numbers in each cell like
this:
1
1
1
0
0
1
0
0
1
1
1

My question are how do I count the largest number of equal cells...