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.

One way - assume your data is in A1:A9

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

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


"johnskate17" wrote:

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.

How about a *non* array formula:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JMB" wrote in message
...
One way - assume your data is in A1:A9

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

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


"johnskate17" wrote:

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.

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 -

"JMB" wrote:

One way - assume your data is in A1:A9

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

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


"johnskate17" wrote:

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.

See this:

http://tinyurl.com/2gq3td

--
Biff
Microsoft Excel MVP


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

"JMB" wrote:

One way - assume your data is in A1:A9

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

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


"johnskate17" wrote:

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.

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.

"T. Valko" wrote:

See this:

http://tinyurl.com/2gq3td

--
Biff
Microsoft Excel MVP

Hey folks,

I have a similar problem that I am struggling to solve. I am trying to calculate the most common answer (I thought MODE) from the same cell (lets say D4) across seven different sheets - each named after a team member. On every sheet D4 contains the same dropdown choices.

For example, the question associated with D4 is, "what is your favorite fruit?" and the dropdown choices a Apple, Banana, Orange, Other. Each person answers on their individual sheet.
I want to know which answer was the most common.

I tried following instructions here and in the following websites, but to no avail...
http://www.mrexcel.com/forum/excel-q...t-strings.html
http://excelusergroup.org/forums/t/1101.aspx
and: https://groups.google.com/forum/?fro...ns/BiYmFxNb77E

I keep getting confused by the quotes ( " ) ( ' ) and ampersands ( & )