+ Reply to Thread
Results 1 to 3 of 3

Most Occurring Number

  1. #1
    Registered User
    Join Date
    02-27-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Most Occurring Number

    I have a spreadsheet that I want to see the 2nd and 3rd most occurring number in a group. I know using the MODE command will give me the most frequent number in the group but is there a way to get the 2nd and the 3rd

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Most Occurring Number

    If you had a range of values in A2:A17, then this gives you the highest most frequent:
    =MODE(A2:A17)

    This gives you the second most frequent, but it's an array formula, enter with CTRL-SHIFT-ENTER to activate the array:
    =MODE(IF(A1:A17<>MODE(A1:A17),A1:A17))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Most Occurring Number

    Actually, this formula suggested in another thread by DonKeyOte appears to do 1st, 2nd and 3rd. For the same range in A2:A17, put this formula in any cell and copy it down 2 more cells...also an array formula, CSE-entered:

    =MODE(IF(ISNA(MATCH($A$2:$A$17, $B$1:$B1, 0)), $A$2:$A$17))

    (reference)

+ Reply to 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