Output the list of frequent data

[Andy Chan]

Dear all,

Here comes two problems.

Problem 1: I have a list of strings (say, in the column A1:A100). How can
I find the "mode" (i.e. the string appearing most frequently in the list?
For example, if the list is

APPLE
APPLE
BANANA
APPLE
CREAM
CREAM
BANANA
CREAM
APPLE
DONUT

then I want the result is APPLE. It seems that the MODE function does not
suppot data type other than numbers. Is there any canned UDF for it?

Problem 2: Following Problem 1, I want to generate a list of the 3 most
frequent data in the list. If the list is the one in the example, I want to
list to be

APPLE
CREAM
BANANA

It is because APPLE is the most frequent datum, followed by CREAM and
BANANA. Any canned UDF for it? (If two data occur equally frequently in the
list, it doesn't matter which comes first.)

Thanks in advance!

Best Regards,
Andy Chan

 

[Jim Cone]

Andy,
You can use a "CountIf" function formula in B1 and fill down.
=COUNTIF($A$1:$A$100,A1)
Then sort on Column B and you get...

APPLE 4
APPLE 4
APPLE 4
APPLE 4
CREAM 3
CREAM 3
CREAM 3
BANANA 2
BANANA 2
DONUT 1

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Andy Chan"
<[email protected]>
wrote in message Dear all,
Here comes two problems.

Problem 1: I have a list of strings (say, in the column A1:A100). How can
I find the "mode" (i.e. the string appearing most frequently in the list?
For example, if the list is

APPLE
APPLE
BANANA
APPLE
CREAM
CREAM
BANANA
CREAM
APPLE
DONUT

then I want the result is APPLE. It seems that the MODE function does not
suppot data type other than numbers. Is there any canned UDF for it?

Problem 2: Following Problem 1, I want to generate a list of the 3 most
frequent data in the list. If the list is the one in the example, I want to
list to be

APPLE
CREAM
BANANA

It is because APPLE is the most frequent datum, followed by CREAM and
BANANA. Any canned UDF for it? (If two data occur equally frequently in the
list, it doesn't matter which comes first.)

Thanks in advance!
Best Regards,
Andy Chan

 

[Biff]

Hi!

Probelm 1:

To find the mode of text values:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

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

Problem 2:

This is a little more complicated. Use a helper column, assume column B. If
desired, you can hide the helper column or put it in some out of sight
location.

Enter this formula in B1 and copy down to B100:

=IF(COUNTIF(A$1:A1,A1)>1,0,COUNTIF(A$1:A$100,A1))

Now, to extract the top 3:

Also entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A1:A100,MATCH(LARGE(B$1:B$100-ROW(B$1:B$100)/10^10,ROWS($1:1)),B$1:B$100-ROW(B$1:B$100)/10^10,0))

Copy down 3 cells or as needed.

Note: you don't necessarily need the first mode formula since the top 3
extracted will also include that result.

Biff

 

[Biff]

Ooops!

Slight correction:

=INDEX(A1:A100,MATCH(LARGE(B$1:B$100-ROW(B$1:B$100)/10^10,ROWS($1:1)),B$1:B$100-ROW(B$1:B$100)/10^10,0))

Better make the row references to A1:A100 absolute before copying:

=INDEX(A$1:A$100,..........................)

Biff