Second most frequent number in a data set in Excel?


G

Guest

I am trying to find the second most frequently occuring value in a given data
set. I know MODE will give most frequent value but I need second, third,
fourth, and fifth most frequent values.
 
Ad

Advertisements

B

Biff

Hmmm....

Is this the unsolvable problem?

Having tried the examples in the links, Bernie's doesn't handle duplicates,
Peo's will only work for the 2nd mode, RD's doesn't handle duplicates, Ron's
doesn't handle duplicates, my attempt also didn't handle duplicates.
Harlan's does handle the duplicates but it needs a trap, otherwise, after
the last unique value is returned the formula starts "randomly" repeating
until it's been copied to more rows than the indexed range. (at which point
it errors as it should)

Harlan's formula with a trap:

=IF(ROWS($1:1)<=SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&"")),INDEX(rng,MATCH(LARGE(FREQUENCY(rng,rng)+(1-ROW(INDIRECT("1:"&(ROWS(rng)+1)))/(ROWS(rng)+1)),ROW(A1)),FREQUENCY(rng,rng)+(1-ROW(INDIRECT("1:"&(ROWS(rng)+1)))/(ROWS(rng)+1)),0)),"")

See the link regarding some strange behavior of
SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&"")):

http://tinyurl.com/jaen9

Biff
 
B

Biff

I found this in my stash!

A1:A20 = numbers (there is at least 1 mode)

Formula in B1:

=MODE(A1:A20)

Formula entered in B2 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=MODE(IF(COUNTIF(B$1:B1,A$1:A$20)=0,A$1:A$20+{0,0}))

Copy down until you get #N/A errors.

Biff
 
Ad

Advertisements

Joined
Mar 21, 2017
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top