Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help finding most common number and so on
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help finding most common number and so on
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 "tht" wrote in message ... 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help finding most common number and so on
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 DataSortdesendingOK Thanks, -- Farhad Hodjat "tht" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding common data | Excel Discussion (Misc queries) | |||
Finding the most common cell duplicate (text) | Excel Discussion (Misc queries) | |||
finding the common names between columns | Excel Discussion (Misc queries) | |||
Finding common Data in multiple worksheets | Excel Discussion (Misc queries) | |||
finding common numbers in large lists | Excel Worksheet Functions |