Mode for text
Is there any formula that will give me the most occurring word in a column of cells. Basically a "mode" formula for words instead of numerical values...
One way to do it would be to use countif, but you will have to fill the entire column next to your series.
For instance, if your values are in range A1:A50, you have to fill this formula between B1 and B50:
countif($A$1:$A$50;A1)
Try:
=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))
adjust ranges to suit and confirmed with CTRL+SHIFT+ENTER not just ENTER,.... you will see {} brackets appear...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thank you. That worked for one column of text date. Is there anyway apply that formular to a scattered range - i.e. A1:A35, B1:B35, and A52:A79. In other words I'd like to find the most occurring word in each of those 3 ranges.... Thanks!
Unless you create a one-column range containing the items in the 3 ranges stacked together and using the formula on that new combined range... then probably VBA would be the way to do it best...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NBVC - you've been a great help! One more thing - is there a way to have that formula not take into account blank cells within the specified range. Let's say my range is a1:a40, but only a1 through a19 has a word in it. The way the formula is written now would return a value of "0". I only want to find the word that is occurring the most, not counting empty cells.
Thanks,
I am guessing that those blanks are results of formulas???
If not, the original formula should work...
otherwise try:
confirmed with CTRL+SHIFT+ENTER=IF(INDEX(A1:A40,MATCH(MAX(COUNTIF(A1:A40,A1:A40)),COUNTIF(A1:A40,A1:A40),0))="",INDEX(A1:A40,MATCH(LARGE(COUNTIF(A1:A40,A1:A40),COUNTBLANK(A1:A40)+1),COUNTIF(A1:A40,A1:A40),0)),INDEX(A1:A40,MATCH(MAX(COUNTIF(A1:A40,A1:A40)),COUNTIF(A1:A40,A1:A40),0)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Because my data set is in 4 separate ranges I have copied the cells in each range into a hidden column of cells so that the formula you provided works. However, when one of the source cells in my ranges doesn't have a value, it shows up as a zero in my copied column and the formula is picking up the "0".
Perhaps adjust my formula to...
Edit:"=IF(INDEX(A1:A40,MATCH(MAX(COUNTIF(A1:A40,A1:A40)),COUNTIF(A1:A40,A1:A40),0))=0,INDEX(A1:A40,MATCH(LARGE(COUNTIF(A1:A40,A1:A40),COUNTIF(A1:A40,0)+1),COUNTIF(A1:A40,A1:A40),0)),INDEX(A1:A40,MATCH(MAX(COUNTIF(A1:A40,A1:A40)),COUNTIF(A1:A40,A1:A40),0)))
Actually, just this might do...
=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,A1:A10),COUNTIF(A1:A10,0)+1),COUNTIF(A1:A10,A1:A10),0))
Last edited by NBVC; 07-14-2008 at 03:00 PM.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks