Consecutive occurences

  • Thread starter Thread starter Antonio
  • Start date Start date
A

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
 
This will work if there's only one number with the most number of occurrances

C1: =MODE(A1:A10)
D1: =COUNTIF(A1:A10,C1)
 
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)

---
 
Tks Max.

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

Tks
 
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.
 
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
 
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
 
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)
 
Back
Top