finding n-th most frequently occuring word
 

I am trying to find the n-th most frequently occurring name is a database. I
have a formula that finds the top most frequently occurring name
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0))
this being entered as an array.

How would I find the second, third, fourth, etc. most frequently occurring
name?

Example:

George
Ilysha
Steven
George
Steven
George

Thanks!

finding n-th most frequently occuring word
 

Sorry, i entered the incorrect array formula, my formula for the top most
frequent is ACTUALLY

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ),0))

"Matt Stayton" wrote:

I am trying to find the n-th most frequently occurring name is a database. I
have a formula that finds the top most frequently occurring name
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0))
this being entered as an array.

How would I find the second, third, fourth, etc. most frequently occurring
name?

Example:

George
Ilysha
Steven
George
Steven
George

Thanks!

finding n-th most frequently occuring word
 

Try these array formulas** :

For the most frequent item:

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

Assume that formula is entered in cell C1.

For the nth most frequent items in descending order, entered in cell C2 and
copied down as needed:

=INDEX(A$1:A$20,MODE(IF(COUNTIF(C$1:C1,A$1:A$20)=0 ,MATCH(A$1:A$20,A$1:A$20,0)+{0,0})))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Matt Stayton" <Matt wrote in message
...
I am trying to find the n-th most frequently occurring name is a database.
I
have a formula that finds the top most frequently occurring name
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0))
this being entered as an array.

How would I find the second, third, fourth, etc. most frequently occurring
name?

Example:

George
Ilysha
Steven
George
Steven
George

Thanks!

finding n-th most frequently occuring word
 

In B1: =INDEX(rng,MODE(MATCH(rng,rng,0)+{0,0}))
In B2: =INDEX(rng,MODE(IF(COUNTIF($B$1:B1,rng)=0,MATCH(rn g,rng,0)+{0,0})))

Ctrl+Shift+Enter on both formula listed above, and copy from B2 down as far
as needed.


"Matt Stayton" wrote:

Sorry, i entered the incorrect array formula, my formula for the top most
frequent is ACTUALLY

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ),0))

"Matt Stayton" wrote:

I am trying to find the n-th most frequently occurring name is a database. I
have a formula that finds the top most frequently occurring name
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0))
this being entered as an array.

How would I find the second, third, fourth, etc. most frequently occurring
name?

Example:

George
Ilysha
Steven
George
Steven
George

Thanks!

finding n-th most frequently occuring word
 

With these formulas my cells say #N/A.

what may i be entering incorrectly?

"Teethless mama" wrote:

In B1: =INDEX(rng,MODE(MATCH(rng,rng,0)+{0,0}))
In B2: =INDEX(rng,MODE(IF(COUNTIF($B$1:B1,rng)=0,MATCH(rn g,rng,0)+{0,0})))

Ctrl+Shift+Enter on both formula listed above, and copy from B2 down as far
as needed.


"Matt Stayton" wrote:

Sorry, i entered the incorrect array formula, my formula for the top most
frequent is ACTUALLY

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ),0))

"Matt Stayton" wrote:

I am trying to find the n-th most frequently occurring name is a database. I
have a formula that finds the top most frequently occurring name
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0))
this being entered as an array.

How would I find the second, third, fourth, etc. most frequently occurring
name?

Example:

George
Ilysha
Steven
George
Steven
George

Thanks!