Count number of times a value appears in role??

[kelvin2088]

Hi everyone,

I have a dataset like this:
0
1
1
1
0
0
0
0
0
1
0
1
0
0
0
0
0
0
0

is there any formula that can help me count maximum number of times each value appears in role? like in this case, "0" appeared in role for maximum 7 times, "1" appeared in role for maximum 3 times

 

[mgirvin]

That is good that it works!

For the example I gave, this is a better formula:

=MAX(FREQUENCY(IF(A1:A24=0,IF(A1:A24<>"",ROW(A1:A24))),IF(A1:A24<>0,IF(A1:A24<>"",ROW(A1:A24)))))


It will give the same results, but is a shorter formula.

 

[mgirvin]

Okay, you want the why? I’ll take a crack at it.

Data set =

Excel Workbook
	A	B
1	=0	0
2	<>0	1
3	empty cell
4	<>0	1
5	<>0	1
6	=0	0
7	=0	0
8	=0	0
9	formula ="
10	=0	0
11	=0	0
12	<>0	1
13	=0	0
14	<>0	1
15	=0	0
16	=0	0
17	formula ="
18	=0	0
19	empty cell
20	empty cell
21	=0	0
22	=0	0
23	=0	0
24	=0	0
...

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The FREQUENCY function has 2 arguments:
<o></o>
=FREQUENCY(data_array,bins_array)
<o></o>
The FREQUENCY function counts data items (data_array) that fall within a given set of categories (bins_array). If data items (data_array) were this set:
<o></o><o></o>
{1;FALSE;FALSE;FALSE;FALSE;6;7;8;FALSE;10;11;FALSE;13;FALSE;15;16;FALSE;18;FALSE;FALSE;21;22;23;24}<o></o><o></o>

And you gave it the categories (bins_array):
<o></o><o></o>
row<=2<o></o>
2< row<= 4<o></o>
4< row<= 5<o></o>
5< row<= 12<o></o>
12< row<= 14<o></o>
14< row
<o></o>
These categories would get a count like this:
<o></o><o></o>
row<=2 counts #s 1 to get ==>> 1<o></o>
2< row<= 4 counts #s to get ==>> 0<o></o>
4< row<= 5 counts #s to get ==>> 0<o></o>
5< row<= 12 counts #s 6,7,8,10,11 to get ==>> 5<o></o>
12< row<= 14 counts #s 13 to get ==>> 1<o></o>
14< row counts #s 15,16,18,21,22,23,24 to get ==>> 7
<o></o>
The max of the numbers 1,0,0,5,1,7 is 7. That is the answer.
<o></o>
If you followed all that, then we can look at how the formula is working.
<o></o><o></o>
To see in more detail, here is the formula:
<o></o>
=FREQUENCY(data_array,bins_array)
<o></o>
Data_array = IF(B1:B24=0,IF(B1:B24<>"",ROW(B1:B24)))
<o></o>
Bins_array = IF(B1:B24<>0,IF(B1:B24<>"",ROW(B1:B24)))
<o></o>
To get:
<o></o>
=MAX(FREQUENCY(IF(B1:B24=0,IF(B1:B24<>"",ROW(B1:B24))),IF(B1:B24<>0,IF(B1:B24<>"",ROW(B1:B24)))))
<o></o>
Data_array = IF(B1:B24=0,IF(B1:B24<>"",ROW(B1:B24))) says: If any values are 0 and not blank then give me the row number. So this evaluates to:
<o></o>
{1;FALSE;FALSE;FALSE;FALSE;6;7;8;FALSE;10;11;FALSE;13;FALSE;15;16;FALSE;18;FALSE;FALSE;21;22;23;24}<o></o>

Bins_array = IF(B1:B24<>0,IF(B1:B24<>"",ROW(B1:B24))) says: If any values are not 0 and not blank then give me the row number. So this evaluates to:<o></o>

{FALSE;2;FALSE;4;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;12;FALSE;14;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}<o></o>

So these numbers: 2,4,5,12,14 are the upper amounts for each category, with one extra category added to the end to catch any values that are greater than the biggest bin value to get:
<o></o>
row<=2<o></o>
2< row<= 4<o></o>
4< row<= 5<o></o>
5< row<= 12<o></o>
12< row<= 14<o></o>
14< row
<o></o>

FREQUENCY is programmed to create categories like this from bin numbers.

The formula then looks like this:
<o></o>
=MAX(FREQUENCY({1;FALSE;FALSE;FALSE;FALSE;6;7;8;FALSE;10;11;FALSE;13;FALSE;15;16;FALSE;18;FALSE;FALSE;21;22;23;24},{FALSE;2;FALSE;4;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;12;FALSE;14;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))<o></o>

Then like this:<o></o><o></o>
=MAX({1;0;0;5;1;7})
<o></o>
And finally:
<o></o><o></o>
Max number of successive 0s = 7.

I hope that helps.<o></o>