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.

Here are some methods on how to get it

http://tinyurl.com/pa35w


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Mr Grim" <Mr wrote in message
...
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.

Check out the link to this post:

http://tinyurl.com/n8vqj

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Mr Grim" <Mr wrote in message
...
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.

i don't think excel's stock vba library provides such a function. you
can either buy a third party library or roll your own.

try the excel programming group for help.

here's an old post that will set you in the right direction:

http://www.exceljockeys.com/forums/f...ay-t12564.html

it's was written to count letter frequency in a string but is
conceptual the same, and easily adapted for, determine the frequency
of unique numbers in cells.

------------------------------------------------------------------------
ExcelJockey's profile: http://www.exceljockeys.com/forums/p...iewprofile&u=4
View this thread: http://www.exceljockeys.com/forums/v...c.php?t=304742

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(FREQUEN CY(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

"Mr Grim" <Mr wrote in message
...
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.

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

"Biff" wrote in message
...
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(FREQUEN CY(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

"Mr Grim" <Mr wrote in message
...
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.