+ Reply to Thread
Results 1 to 11 of 11

How to find the second most frequent number in a list?

  1. #1
    Sam
    Guest

    How to find the second most frequent number in a list?

    Dear all,

    I have a list A1:A100 containing numbers. I know that if I enter a
    formula =MODE(A1:A100) in B1, the most frequent number in the list is shown
    in B1. If I want to know the second most frequent number in the list to be
    stored in B2, what should I enter in B2? Thanks in advance.

    Best Regards,
    Sam



  2. #2
    Bernie Deitrick
    Guest

    Re: How to find the second most frequent number in a list?

    Sam,

    In B2, array enter the formula (enter with Ctrl-Shift-Enter) - all on one
    line (take out any line breaks inserted by the mail reader programs):

    =INDEX(ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100))),MATCH(LARGE(FREQUENCY(A1:A100,ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100)))),2),FREQUENCY(A1:A100,ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100)))),FALSE))

    I'm sure that there is an easier method, which will probably come to me
    shortly after I post this....

    HTH,
    Bernie
    MS Excel MVP


    "Sam" <[email protected]> wrote in message
    news:[email protected]...
    > Dear all,
    >
    > I have a list A1:A100 containing numbers. I know that if I enter a
    > formula =MODE(A1:A100) in B1, the most frequent number in the list is
    > shown in B1. If I want to know the second most frequent number in the list
    > to be stored in B2, what should I enter in B2? Thanks in advance.
    >
    > Best Regards,
    > Sam
    >





  3. #3
    Peo Sjoblom
    Guest

    Re: How to find the second most frequent number in a list?

    Assuming the OP put his MODE formula in B1 this should do


    =MODE(IF(A1:A100<>B1,A1:A100,""))

    array entered


    --
    Regards,

    Peo Sjoblom


    "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
    news:[email protected]...
    > Sam,
    >
    > In B2, array enter the formula (enter with Ctrl-Shift-Enter) - all on one
    > line (take out any line breaks inserted by the mail reader programs):
    >
    > =INDEX(ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100))),MATCH(LARGE(FREQUENCY(A1:A100,ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100)))),2),FREQUENCY(A1:A100,ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100)))),FALSE))
    >
    > I'm sure that there is an easier method, which will probably come to me
    > shortly after I post this....
    >
    > HTH,
    > Bernie
    > MS Excel MVP
    >
    >
    > "Sam" <[email protected]> wrote in message
    > news:[email protected]...
    >> Dear all,
    >>
    >> I have a list A1:A100 containing numbers. I know that if I enter a
    >> formula =MODE(A1:A100) in B1, the most frequent number in the list is
    >> shown in B1. If I want to know the second most frequent number in the
    >> list
    >> to be stored in B2, what should I enter in B2? Thanks in advance.
    >>
    >> Best Regards,
    >> Sam
    >>

    >
    >
    >




  4. #4
    Bernie Deitrick
    Guest

    Re: How to find the second most frequent number in a list?

    See, what did I tell you... it would come to me right after I posted it....
    ;-)

    Thanks, Peo.

    Bernie

    "Peo Sjoblom" <[email protected]> wrote in message
    news:[email protected]...
    > Assuming the OP put his MODE formula in B1 this should do
    >
    >
    > =MODE(IF(A1:A100<>B1,A1:A100,""))
    >
    > array entered
    >
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    >
    > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
    > news:[email protected]...
    >> Sam,
    >>
    >> In B2, array enter the formula (enter with Ctrl-Shift-Enter) - all on one
    >> line (take out any line breaks inserted by the mail reader programs):
    >>
    >> =INDEX(ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100))),MATCH(LARGE(FREQUENCY(A1:A100,ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100)))),2),FREQUENCY(A1:A100,ROW(INDIRECT("A"&MIN(A1:A100)&":A"&MAX(A1:A100)))),FALSE))
    >>
    >> I'm sure that there is an easier method, which will probably come to me
    >> shortly after I post this....
    >>
    >> HTH,
    >> Bernie
    >> MS Excel MVP
    >>
    >>
    >> "Sam" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> Dear all,
    >>>
    >>> I have a list A1:A100 containing numbers. I know that if I enter a
    >>> formula =MODE(A1:A100) in B1, the most frequent number in the list is
    >>> shown in B1. If I want to know the second most frequent number in the
    >>> list
    >>> to be stored in B2, what should I enter in B2? Thanks in advance.
    >>>
    >>> Best Regards,
    >>> Sam
    >>>

    >>
    >>
    >>

    >
    >




  5. #5
    Ron Rosenfeld
    Guest

    Re: How to find the second most frequent number in a list?

    On Sat, 26 Mar 2005 00:42:20 +0800, "Sam" <[email protected]> wrote:

    >Dear all,
    >
    > I have a list A1:A100 containing numbers. I know that if I enter a
    >formula =MODE(A1:A100) in B1, the most frequent number in the list is shown
    >in B1. If I want to know the second most frequent number in the list to be
    >stored in B2, what should I enter in B2? Thanks in advance.
    >
    >Best Regards,
    >Sam
    >


    There's got to be a simpler way, but after some adult beverages, this is what I
    came up with:

    This is an array formula and, after you type or paste it into the cell, hold
    down <ctrl><shift> while hitting <enter> in order to *array-enter*. XL will
    place braces {...} around the formula.

    =MATCH(MATCH(LARGE(FREQUENCY(rng,ROW(INDIRECT(
    MIN(rng)&":"&MAX(rng)))),2),FREQUENCY(rng,ROW(
    INDIRECT(MIN(rng)&":"&MAX(rng)))),0),ROW(
    INDIRECT(MIN(rng)&":"&MAX(rng))),0)

    1. rng is a named range for which you may substitute A1:A100
    2. The "2" in the middle of the second line denotes the 2nd most frequent
    number. You may substitute any other number or a cell reference containing the
    number n for the nth most frequent.


    --ron

  6. #6
    Ron Rosenfeld
    Guest

    Re: How to find the second most frequent number in a list?

    On Mon, 28 Mar 2005 21:48:58 -0500, Ron Rosenfeld <[email protected]>
    wrote:


    >>

    >
    >There's got to be a simpler way, but after some adult beverages, this is what I
    >came up with:
    >
    >This is an array formula and, after you type or paste it into the cell, hold
    >down <ctrl><shift> while hitting <enter> in order to *array-enter*. XL will
    >place braces {...} around the formula.
    >
    >=MATCH(MATCH(LARGE(FREQUENCY(rng,ROW(INDIRECT(
    >MIN(rng)&":"&MAX(rng)))),2),FREQUENCY(rng,ROW(
    >INDIRECT(MIN(rng)&":"&MAX(rng)))),0),ROW(
    >INDIRECT(MIN(rng)&":"&MAX(rng))),0)
    >
    >1. rng is a named range for which you may substitute A1:A100
    >2. The "2" in the middle of the second line denotes the 2nd most frequent
    >number. You may substitute any other number or a cell reference containing the
    >number n for the nth most frequent.
    >
    >
    >--ron


    And I see that Peo came up with a much simpler method!
    --ron

  7. #7
    Harlan Grove
    Guest

    Re: How to find the second most frequent number in a list?

    Peo Sjoblom wrote...
    >Assuming the OP put his MODE formula in B1 this should do
    >
    >=MODE(IF(A1:A100<>B1,A1:A100,""))
    >
    >array entered


    More elegant for the OP's problem. FWIW, it doesn't scale well for,
    say, the 8th most frequently occurring number in a range RNG. That
    requires MATCH(LARGE(FREQUENCY(..))) but with a means of resolving
    different numbers that appear the same number of times. Finding the
    N_th most frequently occurring number in RNG is given by the array
    formula

    =INDEX(RNG,MATCH(LARGE(FREQUENCY(RNG,RNG)
    +(1-ROW(INDIRECT("1:"&(ROWS(RNG)+1)))
    /(ROWS(RNG)+1)),N),FREQUENCY(RNG,RNG)
    +(1-ROW(INDIRECT("1:"&(ROWS(RNG)+1)))
    /(ROWS(RNG)+1)),0))


  8. #8
    Ron Rosenfeld
    Guest

    Re: How to find the second most frequent number in a list?

    On Mon, 28 Mar 2005 21:48:58 -0500, Ron Rosenfeld <[email protected]>
    wrote:

    >On Sat, 26 Mar 2005 00:42:20 +0800, "Sam" <[email protected]> wrote:
    >
    >>Dear all,
    >>
    >> I have a list A1:A100 containing numbers. I know that if I enter a
    >>formula =MODE(A1:A100) in B1, the most frequent number in the list is shown
    >>in B1. If I want to know the second most frequent number in the list to be
    >>stored in B2, what should I enter in B2? Thanks in advance.
    >>
    >>Best Regards,
    >>Sam
    >>

    >
    >There's got to be a simpler way, but after some adult beverages, this is what I
    >came up with:
    >
    >This is an array formula and, after you type or paste it into the cell, hold
    >down <ctrl><shift> while hitting <enter> in order to *array-enter*. XL will
    >place braces {...} around the formula.
    >
    >=MATCH(MATCH(LARGE(FREQUENCY(rng,ROW(INDIRECT(
    >MIN(rng)&":"&MAX(rng)))),2),FREQUENCY(rng,ROW(
    >INDIRECT(MIN(rng)&":"&MAX(rng)))),0),ROW(
    >INDIRECT(MIN(rng)&":"&MAX(rng))),0)
    >
    >1. rng is a named range for which you may substitute A1:A100
    >2. The "2" in the middle of the second line denotes the 2nd most frequent
    >number. You may substitute any other number or a cell reference containing the
    >number n for the nth most frequent.
    >
    >
    >--ron



    And by the light of morning I see that this formula will only work for
    integers. So forget it if your data set is not so limited, and use one of the
    other posted options.



    --ron

  9. #9
    Registered User
    Join Date
    09-13-2012
    Location
    essoarde 1st
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to find the second most frequent number in a list?

    I used the formula Ron included here, and it works when I randomly generate numbers in a new excel sheet. However, if I try to use the formula in a sheet of data, for some reason it keeps giving me a #REF failure.

    I can't figure out why. Help?

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How to find the second most frequent number in a list?

    pfallonj,

    Welcome to the Forum, unfortunately:

    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. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  11. #11
    Registered User
    Join Date
    04-21-2015
    Location
    Zimbabwe
    MS-Off Ver
    2007
    Posts
    3

    Re: How to find the second most frequent number in a list?

    Please Help - I am trying to find the most common occurring set of numbers in an array need formula and doing manual is virtually impossible. If you can explain how to post and example I can attached.

+ 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