+ Reply to Thread
Results 1 to 4 of 4

10 most common numbers in a block

Hybrid View

  1. #1
    Registered User
    Join Date
    05-17-2017
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    4

    10 most common numbers in a block

    Hello, I've looked at some of the formulas in this section regarding most common numbers. I just cant figure out how to get 10.
    I know I'm going to use the =mode formula. My block of numbers is A1:A300 through E1:300 which includes B,C, and D
    I just can't figure out the formula for the most common out of the block. I can get the most common for the row, for instance row
    A. That's fairly simple.
    I appreciate any help on this, thanks so much, diana

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 10 most common numbers in a block

    The Mode function can return the most common number in a 2D range like A1:E300

    You'll need an array formula to get the 2nd 3rd etc.

    For the 1, use this in say G2 for example
    =MODE(A1:E300)

    Then in G3 put this array formula entered with CTRL + SHIFT + ENTER
    =MODE(IF(ISNA(MATCH($A$1:$E$300,$G$2:$G2,0)),$A$1:$E$300))
    And fill down to G11

  3. #3
    Registered User
    Join Date
    05-17-2017
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    4

    Re: 10 most common numbers in a block

    That was quick Jonmo1, Thank you so much!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 10 most common numbers in a block

    You're welcome.

    If you had any blanks in the range, then the MODE(IF array formula would consider those blanks 0 and would be counted.

    Here's an updated version to account for that.
    =MODE(IF(ISNA(MATCH($A$1:$E$300,$G$2:$G2,0)),IF(ISNUMBER($A$1:$E$300),$A$1:$E$300)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 09-06-2016, 01:21 PM
  2. Most common numbers in group of random numbers
    By Axxek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2013, 11:19 AM
  3. How to find the most common numbers?
    By alikirca20 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-04-2013, 09:34 AM
  4. Block different than 8 numbers to defined cells range
    By demonofhell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-28-2012, 07:57 AM
  5. Replies: 5
    Last Post: 10-09-2012, 10:44 AM
  6. [SOLVED] Pull out the Top five most common numbers in a block of numbers
    By caliskier in forum Excel General
    Replies: 8
    Last Post: 09-10-2010, 02:44 PM
  7. HOW DO I BLOCK DUPLICATE NUMBERS IN EXCEL
    By SCOTT in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-11-2006, 04:50 PM

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