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

Sam

unread,

Mar 25, 2005, 11:42:20 AM3/25/05

to

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

@consumerdotorg Bernie Deitrick

unread,

Mar 28, 2005, 8:47:42 PM3/28/05

to

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" <sam_w...@yahoo.com.hk> wrote in message
news:d21f1c$d6...@imsp212.netvigator.com...

Peo Sjoblom

unread,

Mar 28, 2005, 9:03:01 PM3/28/05

to

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:etyxNFAN...@TK2MSFTNGP09.phx.gbl...

@consumerdotorg Bernie Deitrick

unread,

Mar 28, 2005, 9:18:56 PM3/28/05

to

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

Thanks, Peo.

Bernie

"Peo Sjoblom" <ter...@mvps.org> wrote in message
news:ukJY5NAN...@tk2msftngp13.phx.gbl...

Ron Rosenfeld

unread,

Mar 28, 2005, 9:48:58 PM3/28/05

to

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

Ron Rosenfeld

unread,

Mar 28, 2005, 9:57:04 PM3/28/05

to

On Mon, 28 Mar 2005 21:48:58 -0500, Ron Rosenfeld <ronros...@nospam.org>
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

Harlan Grove

unread,

Mar 29, 2005, 3:52:47 AM3/29/05

to

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))

Ron Rosenfeld

unread,

Mar 29, 2005, 7:12:29 AM3/29/05

to

On Mon, 28 Mar 2005 21:48:58 -0500, Ron Rosenfeld <ronros...@nospam.org>
wrote:

>On Sat, 26 Mar 2005 00:42:20 +0800, "Sam" <sam_w...@yahoo.com.hk> wrote:

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

ivy.ga...@gmail.com

unread,

Jun 8, 2013, 2:39:27 PM6/8/13

to

I had used this forumla in my spreadsheet to compute the second most occuring value: =MODE(IF(($J49:$CF49="")+($J49:$CF49=$F49),"",$J49:$CF49))

Please note that F49 is where the actual mode (most occurring) value is stored and that J49 to CF49 is the array I'm testing. Just ignore the $ signs - they're important only to my particular spreadsheet.

I just don't understand what the double quotes mean. Do they mean to return nothing? I notice that you use them in your formula too. If that's what they mean, then why can't I just have MODE(IF($J49:$CF49=$F49, "", $J49:$CF49))
What's the significance of testing $J49:$CF49=""

Thanks - if anyone can explain that, that would be great. I don't just want a formula that works, I want to know how it works! :)

ivy.ga...@gmail.com

unread,

Jun 8, 2013, 2:47:09 PM6/8/13

to

On Monday, March 28, 2005 8:03:01 PM UTC-6, Peo Sjoblom wrote:

Also, the formula =MODE(IF(A1:A100<>B1,A1:A100,"")) didn't work for me :( It just returned 0 which is definitely not the 2nd most frequent value.

Claus Busch

unread,

Jun 8, 2013, 2:59:41 PM6/8/13

to

Hi Ivy,

Am Sat, 8 Jun 2013 11:47:09 -0700 (PDT) schrieb ivy.ga...@gmail.com:

> Also, the formula =MODE(IF(A1:A100<>B1,A1:A100,"")) didn't work for me :( It just returned 0 which is definitely not the 2nd most frequent value.

the formula is a array formula to be entered with CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch

unread,

Jun 8, 2013, 3:12:59 PM6/8/13

to

Hi Ivy,

Am Sat, 8 Jun 2013 11:47:09 -0700 (PDT) schrieb ivy.ga...@gmail.com:

> Also, the formula =MODE(IF(A1:A100<>B1,A1:A100,"")) didn't work for me :( It just returned 0 which is definitely not the 2nd most frequent value.

if you want all numbers in rank order write a 0 in B1 and in B2:
=MODE(IF(ISERROR(MATCH(A$1:A$100,B$1:B1,0)),A$1:A$100))
and enter the formula with CTRl+Shift+Enter and copy down till you get
N/A

Message has been deleted

iog...@gmail.com

unread,

Mar 20, 2017, 8:44:19 PM3/20/17

to

DID YOU FIND THE FORMULA?
Well I have 7 columns x 3056 rows = 21,392 values

I just applied:

=MODE(A1:G3056)

Gave me '12' as #1 with 484 occurrences, then I deleted all the 12's with Replace (Ctrl+H) and showed me the #2 XD

I just need it the 6 most repeated... took over 5mins instead of 15mins reading formulas on the internet and watching videos in youtube

Zaidy036

unread,

Mar 20, 2017, 8:59:24 PM3/20/17

to

On 3/20/2017 8:10 PM, iog...@gmail.com wrote:

> Do you have the formula?
>
Look at free ASAP Utilities.

Under "Information" there are various functions to count different
unique numbers in a list and duplicates.