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
---

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

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
---

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

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
---

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

"Max" wrote:

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
---

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
---

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)

--
Biff
Microsoft Excel MVP


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

Tks all for the precious help...

with this one obtained the desired result



"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Antonio" wrote in message
...
Tks all for the precious help...

with this one obtained the desired result



"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


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