Mode function in excel using Text

[Guest]

I am having trouble finding a function in excel that is similar to the MODE
function, but can work using a column of text, and return the phrase that is
found most often.

 

[Guest]

One way - assume your data is in A1:A9

=INDEX(A1:A9,MATCH(MAX(COUNTIF(A1:A9,A1:A9)),COUNTIF(A1:A9,A1:A9),0))

array entered w/Cntrl+Shift+Enter (or you'll likely get an error).

 

[Ragdyer]

How about a *non* array formula:

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

 

[Guest]

Hi Guys,

Im also doing something similar to this and i notice that this array uses
the "MAX" function .. is there any way to adapt this array to find the 2nd
most common and 3rd most common ? .. the same way in which you would use the
"LARGE" function ?

Michael -

 

[T. Valko]

See this:

http://tinyurl.com/2gq3td

 

[Guest]

Thanks for that but unfortunately it wont help in what im trying to do,
here's some some background .. for a number of months there has been an
ongoing spreadhseet which is filled in daily.

This sheet holds many different forms of data and unfortunately means that
when i want to search for the highest 3 runners i have to search
conditionally using a week number

the previous posters help allowed me to search within the condition for the
most often occuring but i also need the 2nd and 3rd most common.

when the data is analised its done so from a week to week basis and so the
ability to search from week to week is a necessity.