Most common
Posted by nataliez on November 22, 2000 6:31 AM
Is there a function which returns the most-commonly occuring word or phrase in a column? The MODE function only seems to work on numeric values.
Posted by Tim Francis-Wright on November 22, 2000 2:25 PM
The nifty way to do this would be to write
a user-defined function in VBA to do the hard work.
The way to do this without VBA is a bit ugly,
but works well enough.
Let's say that you want to see which cell contents
are most common in column C. (If you need
which WORD in the column is most common, then this
won't help.)
Insert a column D, which can be hidden after
you put formulas in it. The formula for D1 is:
=COUNTIF(C1,C:C)
Copy this down as far as you need.
To get the most common cell in C, somewhere
(not in columns C or D) have the formula:
=INDIRECT(ADDRESS(MATCH(MAX(D:D),D:D,0),COLUMN(C:C)))
HTH
Posted by Tim Francis-Wright on November 22, 2000 2:25 PM
The nifty way to do this would be to write
a user-defined function in VBA to do the hard work.
The way to do this without VBA is a bit ugly,
but works well enough.
Let's say that you want to see which cell contents
are most common in column C. (If you need
which WORD in the column is most common, then this
won't help.)
Insert a column D, which can be hidden after
you put formulas in it. The formula for D1 is:
=COUNTIF(C1,C:C)
Copy this down as far as you need.
To get the most common cell in C, somewhere
(not in columns C or D) have the formula:
=INDIRECT(ADDRESS(MATCH(MAX(D:D),D:D,0),COLUMN(C:C)))
HTH
Posted by Aladin Akyurek on November 23, 2000 12:11 AM
Tim: Your solution is generic. Even better than MODE which is restricted to 30 numbers. It delivers the most common word too. So I don't understand your statement between parens!
You meant of course:
=COUNTIF(C:C,C1)
better:
=COUNTIF(C:C,""&C1)
Aladin
Posted by Aladin Akyurek on November 23, 2000 12:11 AM
Tim: Your solution is generic. Even better than MODE which is restricted to 30 numbers. It delivers the most common word too. So I don't understand your statement between parens!
You meant of course:
=COUNTIF(C:C,C1)
better:
=COUNTIF(C:C,""&C1)
Aladin
