need help finding most common number and so on

[Guest]

can anyone please tell me how to make the most common, the second most
common, the third most common, fourth most common, the fourth most common,
the fifth most common, and the sisth most common numbersshow up in six
different cells? I can get the most common, but not the following.

I am using cells c3:h204 for my data range.
my data is;

10 14 22 42 43 1
2 7 11 22 36 35
13 23 24 30 44 5
6 13 42 46 56 42
28 30 33 48 54 25
16 24 41 43 54 36
4 9 20 45 55 34
11 16 31 52 53 42
5 7 26 38 56 15
8 10 35 36 43 14
1 9 10 23 53 40
1 4 11 31 47 37
11 14 21 25 26 33
7 15 16 19 28 10
24 32 34 36 39 6
16 26 33 34 46 38
15 23 37 48 53 22
18 25 34 35 42 6
18 21 35 51 53 36
7 21 46 49 55 15
17 25 36 40 43 9
7 11 16 38 49 35
10 13 25 42 43 30
16 22 29 39 42 20
14 21 33 35 51 43
18 31 44 45 48 18
3 18 21 38 50 43
1 9 26 46 51 11
17 35 40 46 48 41
3 9 24 29 41 41
32 39 46 48 49 41
16 21 35 36 46 38
22 33 35 40 53 15
14 18 44 52 56 25
16 17 36 49 54 14
3 5 15 26 53 35
4 28 30 31 35 17
4 8 15 33 52 10
12 14 26 40 42 22
7 11 26 38 54 13
2 12 44 46 51 6
9 10 38 51 53 1
3 4 10 39 50 29
7 12 25 44 53 3
5 12 15 25 34 43
9 32 37 42 48 7
6 7 17 28 40 39
20 30 31 35 49 23
1 15 29 32 45 8
6 9 13 43 46 45
16 22 23 37 53 35
7 14 24 41 56 7
14 35 40 47 48 35
7 13 20 42 47 9
5 19 25 30 50 42
9 20 24 25 36 23
11 42 52 53 55 28
13 22 33 51 52 42
8 10 22 25 55 22
5 34 40 45 46 21
15 22 26 30 32 31
5 25 41 48 51 35
9 13 23 29 54 34
6 18 20 28 38 37
24 27 42 47 50 8
14 30 35 40 43 2
1 11 20 21 46 18
6 19 32 33 40 39
3 25 43 45 55 40
3 6 38 42 45 30
7 12 17 22 43 16
2 19 44 45 56 43
6 26 33 39 55 1
3 16 25 30 44 42
9 17 34 52 53 2
1 32 36 42 53 4
5 6 51 53 55 12
15 25 37 38 52 4
17 24 35 46 54 33
3 4 5 7 36 16
5 12 13 46 50 10
10 12 22 44 48 16
14 16 38 40 49 29
1 5 13 18 33 30
2 24 31 50 55 44
14 29 32 43 49 14
2 13 23 32 35 4
7 21 24 41 51 10
18 26 35 36 43 24
12 13 29 49 52 20
13 25 26 28 56 39
5 14 34 36 52 42
7 15 27 46 56 39
9 15 31 42 45 41
20 40 46 48 54 27
2 14 20 29 44 32
13 17 24 34 56 24
11 21 37 53 54 12
27 30 36 38 45 13
1 20 23 24 33 29
8 17 18 26 47 37
5 29 35 52 53 9
3 10 18 36 38 41
2 13 28 34 45 36
12 14 20 47 48 24
17 21 28 48 54 1
5 12 31 51 56 1
7 24 40 48 50 15
6 36 39 45 52 45
7 15 24 43 44 22
8 20 39 53 55 10
7 11 22 27 31 33
16 29 32 36 55 12
1 20 32 37 39 9
2 4 7 27 41 4
13 14 25 34 50 6
8 10 18 29 33 10
2 12 45 46 56 20
1 18 31 46 52 37
9 25 48 51 56 7
4 7 19 50 52 15
14 18 35 39 49 14
1 2 17 47 49 19
4 16 17 28 31 8
8 11 23 48 52 5
27 28 30 42 50 22
4 17 18 51 54 31
15 27 36 38 42 32
25 29 38 39 46 4
2 4 35 36 48 22
3 5 12 16 34 27
14 23 27 36 45 36
16 25 31 43 46 28
27 36 43 49 54 33
24 39 40 43 46 2
2 16 25 30 48 26
29 31 32 41 52 42
1 35 53 54 56 44
2 7 8 18 29 46
31 34 36 51 55 4
18 22 28 44 53 46
24 32 37 39 40 44
5 21 27 44 53 36
7 27 32 37 38 30
8 11 28 37 53 12
15 19 20 32 38 21
14 20 25 40 44 37
11 24 27 49 55 23
10 37 39 49 54 8
6 20 23 40 56 36
14 25 26 31 56 17
9 10 12 22 41 4
14 15 31 32 43 20
6 10 26 30 33 16
3 12 21 38 44 45
7 8 47 51 52 5
5 25 31 33 34 41
9 22 37 41 43 30
8 18 21 42 46 11
2 4 5 40 48 7
9 14 34 50 51 40
8 16 21 25 27 16
7 9 41 53 54 38
5 18 21 28 36 20
8 17 25 28 53 1
7 12 18 31 55 30
11 17 28 29 36 42
3 12 16 32 33 15
6 20 24 25 34 44
24 30 42 53 54 20
2 4 23 27 36 37
12 24 28 29 36 41
1 3 14 30 52 10
14 17 26 27 28 5
6 7 20 41 51 38
35 36 40 42 52 45
5 16 41 46 50 1
13 24 44 48 52 30
15 28 43 51 52 11
1 8 27 31 50 40
1 4 14 45 53 33
19 32 42 49 56 29
5 20 38 47 54 35
6 7 8 13 40 12
2 13 18 36 46 36
9 15 20 24 55 3
8 37 38 45 54 21
13 35 36 43 52 5
3 5 48 50 53 4
17 22 39 50 52 46
4 16 23 25 40 22
1 10 18 29 55 8
7 18 27 35 54 34
7 13 48 51 54 11
9 13 25 36 48 2
5 17 32 39 53 36
9 23 45 48 50 3
22 38 48 50 55 29
14 25 41 42 50 40
2 20 37 43 46 4
14 43 44 50 56 7

I had a couple of people try earlier, but I must be an ideot. I could'nt
make their ideas work.
thanks...

 

[T. Valko]

Now that we know you have 6 columns of numbers.....

C3:H204 = named range = rng

Enter this formula in A3:

=MODE(rng)

Enter this array formula** in A4 and copy down a total of 5 cells (or as
needed):

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(COUNTIF(A$3:A3,rng)=0,rng)),"")

Enter this formula in B3 and copy down as needed:

=COUNTIF(rng,A3)

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

Biff

 

[Guest]

Hi,

The other way is as follow:

assumed your data is in the range A1:F200

in the cell G1 enter:
=MIN(A1:F200)
in the cell G2 enter:
=MAX(A1:F200)
make a serial number from your min numbet to max number for example if your
min number is 1 and your max number is 50 make a serial number fro 1 to 50 in
the cells G3 to G52
in the cell H3 enter:
=COUNTIF($A$1:$F$200,G3)
copy drag cell H3 to H52
active cells G3 to H52 and Data>Sort>desending>OK

Thanks,