Pull out the Top five most common numbers in a block of numbers

I have a large block of numbers in a spreadsheet and I am wondering how I can look at the whole block and pull out the top five most common numbers. So perhaps the number 4 is repeated the most times, it would be number 1, number 18 might be repeated the second most number of times so it would be in the number 2 slot etc etc. Any tricks to doing this?

one way

J4: =MODE(IF(ISNA(MATCH($C$3:$G$29,$J$3:$J3,0)),$C$3:$G$29))
committed with CTRL + SHIFT + ENTER

once set copy J4 to J5:J8

You will only need to edit the formula & reset the array if you're altering the range to which you're referring (eg C3:G29), the values that appear in the results section (J4:J8) will alter as the values in the range alter.

From XL Help:

MODE:
Returns the most frequently occurring, or repetitive, value in an array or range of data.


So let's say A2:A11 contains the following:

Please Login or Register  to view this content.

Thus: =MODE(A2:A11) returns 2

In your case however things are not quite so trivial, certainly not beyond the most common value... this is because you must exclude any numbers already returned when conducting your test.

If we say we want to return the top 3 values from A2:A11 in terms of their frequency in cells B2:B4 we use:

B2: =MODE(IF(ISNA(MATCH($A$2:$A$11,$B$1:$B1,0)),$A$2:$A$11))
committed with CTRL + SHIFT + ENTER

What this is doing is populating an array with ten values (one for each cell in A2:A11), if the number in A2:A11 can not be found in B1 (ie ISNA = TRUE) then the number from A2:A11 is returned to the Array, else a Boolean of FALSE is returned...

In the case of B2 (most common) no value in A2:A11 can be found in B1 so you end up with an array of:

{1;2;3;2;2;2;1;4;4;1}

This is then used in the MODE and the most common value is 2.

If we now shift to the 2nd most common value in A2:A11 our formula once copied from B2 will read as:

B3: =MODE(IF(ISNA(MATCH($A$2:$A$11,$B$1:$B2,0)),$A$2:$A$11))
(again committed with CSE)

Now in this case some of the values in A2:A11 will be found in the preceding range of B1:B2 given B2 contains 2... so you end up with an Array of:

{1;FALSE;3;FALSE;FALSE;FALSE;1;4;4;1}

You will note the FALSE replace the instances of 2...

The MODE will look only at the remaining numerical values (ie Booleans are ignored) so the most common value in that array is 1

To finish the example - the third most common value is generated in the next cell:

B4: =MODE(IF(ISNA(MATCH($A$2:$A$11,$B$1:$B3,0)),$A$2:$A$11))
(again committed with CSE)

This generates the following Array:

{FALSE;FALSE;3;FALSE;FALSE;FALSE;FALSE;4;4;FALSE}

Both 2 & 1 are now replaced by FALSE as these appear in the prior rows (B1:B3) and should thus be discounted from this calculation... the result of which will be 4.

Be sure to make use of XL Help and the Evaluate Formula option which can be very helpful when it comes to discerning the nature of what a formula is doing on execution.

I hope that helps.

EDIT: I should add that for B2 you could simply use MODE(A2:A11) however for consistency I applied the same formula to each cell in the results range, in terms of the above B2:B4 - in your file J4:J8.

Alright so please excuse the lack of knowledge of excel, but I was reading the page on pulling out block numbers and it all makes sense but the ctrl+shift+enter for the array didnt work?? Probably just an idiot but whatever that's why I talking to the smart guys.
I have some other crap that I'm playing around with nevermind the other stuff just the U15
Thank you very much for any help that you can give ;-)

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.