Consecutive occurences

[Antonio]

Hi all

have data displayed on column C as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2
4
4
4
4
4

Is there a way to count the large number of occurences of the same number,
which in this case should be number 4 five times??

Tks in advance

 

[Barb Reinhardt]

This will work if there's only one number with the most number of occurrances

C1: =MODE(A1:A10)
D1: =COUNTIF(A1:A10,C1)

 

[Antonio]

Tks 4 the help. but the result I get is the same as if I only use the countif
function

 

[Max]

How about adapting the expression that Ron C. gave you in an earlier posting?
To suit it here for your col C data in C1:C28, try array-entered:
=MAX(FREQUENCY(IF(C1:C27=(C2:C28),ROW(C1:C27)),IF(C1:C27<>(C2:C28),ROW(C1:C27))))+(COUNT(1/(C1:C27=(C2:C28)))>0)

---

 

[Don]

then combine them together?
=MODE(A3:A22)&" = "&COUNTIF(A3:A22,MODE(A3:A22))

 

[Antonio]

Tks Max.

Tried it, but, got the result n/a..., will copy past your to see the result

Tks

 

[Max]

Tried it, but, got the result n/a...,

Try this quick sample which shows it working the way it should:
http://www.freefilehosting.net/download/3g6h9
Max Consecutives in Col.xls


---

 

[Tyro]

What does "the large number of occurences of the same number" mean?

Tyro

 

[T. Valko]

That can be reduced to:

=MAX(FREQUENCY(IF(C1:C27=C2:C28,ROW(C1:C27)),IF(C1:C27<>C2:C28,ROW(C1:C27))))+1

Note that it will count empty cells as consecutives *and* empty cells and
numeric 0 will evaluate as the same.

 

[Antonio]

Tks Max + Valko

Both formulas work, in giving the largest number of occurences, but unable
to specify if it is from number one, two, three....

Any ideas??

Tks

 

[Antonio]

Hi

the formula presently used as follows:

=MAX(FREQUENCY(IF(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)=B10:OFFSET(INDEX(B:B;MATCH(99^99;B:B));0;0);ROW(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)));IF(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)<>B10:OFFSET(INDEX(B:B;MATCH(99^99;B:B));0;0);ROW(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)))))+1

but still unable to get the desired result, that for the given set of
numbers should be:
1 = 0 (consecutive)
2 = 0 (consecutive)
3 = 2 (Consecutive)
4 = 5 (Consecutive)
5 = 0
6 = 0

Tks for the assistance n
bst rgds

 

[T. Valko]

Assume your range of numbers are in B9:B30.

List the unique numbers starting in D9 on down.

Enter this array formula** in E9 and copy down as needed:

=MAX(FREQUENCY(IF(B$9:B$30=D9,ROW(B$9:B$30)),IF(B$9:B$30<>D9,ROW(B$9:B$30))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Antonio]

Tks all for the precious help...

with this one obtained the desired result

 

[T. Valko]

You're welcome. Thanks for the feedback!