Closed Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    266

    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?
    Attached Files Attached Files
    Last edited by caliskier; 04-07-2009 at 01:18 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

    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

  3. #3
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    266

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

    Is there a way to do it without haveing to Commit with the CTRL + SHIFT + ENTER. Don't you have to do that every time you change a number in the box? We need this to be a live file that will be constantly changing.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

    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.

  5. #5
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    266

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

    Quote Originally Posted by DonkeyOte View Post
    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.
    Wow, ok, thanks a bunch, would you mind breaking down how this formula works?

    =MODE(IF(ISNA(MATCH($C$3:$G$29,$J$3:$J3,0)),$C$3:$G$29))

    ISNA looks for #NA, got that, what does Mode mean and how does it know to pick the next of the top five when you do 2nd, 3rd, etc.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

    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.
    Last edited by DonkeyOte; 04-07-2009 at 01:12 PM.

  7. #7
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    266

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

    WOW, very nice, and very nicely explained. Thank you for your time and your help.

  8. #8
    Registered User
    Join Date
    09-10-2010
    Location
    michigan
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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 ;-)
    Attached Files Attached Files
    Last edited by robomike; 09-10-2010 at 02:47 PM.

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

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

    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.
    Audere est facere

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1