Most Frequently used Text value in a column

[news.newsguy.com]

I have an Excel 2000 spreadsheet used for daily entries for any given month.
Some of the columns have text values instead of numeric. It is possible
that the text entries would be the same for every day or they could vary
between 2 or 3 different text values. I would like to know if there is a
formula that would retrieve the most frequently entered TEXT.

Ex:

Jan 01 cat
Jan 02 dog
Jan 03 cat
Jan 04 dog
Jan 05 dog
Jan 06 dog
Jan 07 cat
Jan 08 dog
Jan 09 bird
Jan 10 cat
=============
Max dog

"dog" should be retrieved because its frequency was 5 while "cat" occurred 4
times and "bird" only once.

Thanks,
Ron

 

[Frank Kabel]

Hi Ron

try the following
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),
0),1)

you have to enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank

 

[Peo Sjoblom]

One way

=INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))


where B1:B10 holds your animals

 

[Frank Kabel]

Hi Peo
i'm impressed! Never used MODE in combination with MATCH before. This
is definetly shorter than my approach and does not require an array
formula

Regards
Frank

 

[news.newsguy.com]

Thanks for the response that helped a lot.
What suggestion would you have for a change to the formula if there was no
entry for a given day.

Ex:
Jan 01 cat
Jan 02 dog
Jan 03 cat
Jan 04 dog
Jan 05 dog
Jan 06 dog
Jan 07
Jan 08 dog
Jan 09 bird
Jan 10 cat
=============
Max dog

The current formula returns a #N/A error.

Thanks,
Ron

 

[Peo Sjoblom]

You could add this to the formula

=INDEX(B1:B10,MODE(IF(B1:B10<>"",MATCH(B1:B10,B1:B10,0))))

although now it has to be entered with ctrl + shift & enter, or you could
use
Frank's array formula

=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)

 

[news.newsguy.com]

Hopefully I'm not being a pest but here's another question concerning this
formula and a little more background info.

The spreadsheet for which I need this formula is used in the following
manner.
Column A is the day/date of the month and the other columns are used for
flows or other operational data. I have a couple of columns in which the
user will enter the name of a chemical added to a process. Under the row for
the last day of the month is the AVERAGE row. I can average the numeric
entries with no problem. I wish to retrieve the chemical name used most
frequently during the month in this AVERAGE cell for that column.

It may be that no chemicals were used and the Average cell should be blank.
Multiple chemicals could be used and the most frequently used (or in the
case of a tie the first chemical listed) would be the value in the Average
cell. Any given day could have either a chemical name entered or be blank.

I can modify your formula to cover the instance where no entries are made so
that the AVERAGE cell is empty.

=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,
0)))))
But if there is only 1 entry for the month then the formula returns the #N/A
error. If there are at least 2 entries then it works.
Is there anyway to find and return the value for a single entry in a column
when the other cells are empty?


Frank's array formula works for either all empty or a single entry but if
the first cell (B1) is empty then it doesn't return anything regardless of
how many other entries were made. Is there anyway to fix that?
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)

I do appreciate the help from both of you.
Ron

 

[Frank Kabel]

news.newsguy.com wrote:
[snip]

Frank's array formula works for either all empty or a single entry
but if the first cell (B1) is empty then it doesn't return anything
regardless of how many other entries were made. Is there anyway to
fix that?

=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),
0),1)

Hi
I tried this formula again and i had no problem if B1 is empty. If you
like, send me your spreadsheet and I will have a look at it

Frank

 

[Peo Sjoblom]

Frank's formula works if the first cell is empty.. Regardless the reason the
error with one value only is the mode function,
try to put single number in a range and use mode

=MODE(A1:A10)

mode needs at least 2 values, so you have to condition that before the mode
part, also note that mode will return an error
if all values are unique so if you have 2 entries and they are "x" and "y"
mode will retrain an error even if you would dodge the single entry with
something like

=IF(COUNTA(A1:A10)=0,"",IF(COUNTA(A1:A10)=1,INDEX(A1:A10,MATCH(TRUE,A1:A10<>
"",0)),INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))))

However I would use this

=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNT
IF(A1:A10,A1:A10),0),1))

array entered

--

Regards,

Peo Sjoblom

 

[news.newsguy.com]

Peo Sjoblom and Frank

Thanks for all the help on this problem - everything is working correctly
for me now.

Ron