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)
--
HTH,
Barb Reinhardt



"Antonio" wrote:

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

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



"Barb Reinhardt" wrote:

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

C1: =MODE(A1:A10)
D1: =COUNTIF(A1:A10,C1)
--
HTH,
Barb Reinhardt



"Antonio" wrote:

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

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Tks Max.

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

Tks

"Max" wrote:

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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);R OW(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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Antonio" wrote:

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



"Barb Reinhardt" wrote:

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

C1: =MODE(A1:A10)
D1: =COUNTIF(A1:A10,C1)
--
HTH,
Barb Reinhardt



"Antonio" wrote:

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

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

Tyro

"Antonio" wrote in message
...
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