Is there a formula that will find a mode of a series of text fields. ie the moste reoccuing text string in a list?
Tools funiton
Posted on Dec 07, 2008
Name the list as a range. Copy the list to a different location, eliminate duplicate values, then write the formula next to the newly listed items to count the number of occurrences in the original list range. Thanks for rating FixYa!
Posted on Apr 23, 2008
I will help you write the exact formula if you can give me some more definite information. For instance, is the list a relatively stable length? Are the choices for the text within the list limited to a certain number of words/phrases?
For example, assuming the choices are going to be fairly stable, the copying suggested above would only have to be done once (to set the "choice" list for counting). Then, by naming the range in which the choices are to be counted, we can compare them to the specified values and count. Then, we put in a min/max type formula to give you a one-cell answer (if that is what we're aiming for...) I can certainly give you the formula if you can give me more info - I would like very much to get to FixYa!
If you are still having an issue with which I might assist, I would like to get to FixYa!
martaw:
If you are still having an issue, I would like to get to help. If you could just give me some more information, as indicated earlier, we can work out formulas for you. As you know, mode is a numerical/mathematical term, but we can get a maximum count on text entries, I would just need to know a little more about what types of issues you face. Thanks, and I look forward to you feeling comfortable enough to rate this at FixYa!
martaw:
Could you please take another look at the post here, and my attempts to provide you with further help? I responded in the best possible way to the original question, which did not indicate this was a recurring need, nor is it a one-phrase response with the need you have. I have offered several times, and continue to offer, additional help in reaching the exact formula you need, as I am an expert specifically in Excel and related functions and formulas. If your issue has been resolved, I would appreciate it very much if you could rerate the response, as all ratings are calculated in an expert's ranking and status, and I have provided not only valuable information, but also numerous attempts to continue to help if you have not found the best solution through the advice I have provided. If you still need help, please repost; otherwise, I would appreciate it if you would consider rerating the level of information provided in my response - hopefully it is at a FixYa! Thank you for your consideration.
I am back again even though I haven't heard back. The best way to do what you want is to create a pivot table that would count your various text entries. If you need help with this see http://office.microsoft.com/en-us/excel/....
Then if you need the information returned to another place in the file regarding the one with the maximum count, it is quite easy to pull out the maximum value, using a getpivotdata formula. Again, if you need help with this please post back. Otherwise, please rate at FixYa!
×
Hi,
A 6ya expert can help you resolve that issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
Good luck!
Posted on Jan 02, 2017
Oct 01, 2010 | Microsoft Office Excel 2007 Upgrade:...
on Nov 11, 2013 | Microsoft Excel Computers & Internet
Nov 05, 2009 | Microsoft Office Excel 2003 for PC
Jul 21, 2009 | Microsoft Office Excel 2007
May 07, 2009 | Microsoft Excel 2007 Home and Student...
Nov 18, 2008 | Microsoft Computers & Internet
Sep 15, 2008 | Computers & Internet
Aug 03, 2008 | Microsoft Windows XP Professional
Jul 29, 2008 | Microsoft Excel for PC
215 people viewed this question
Usually answered in minutes!
I need this to be more automated for forms that are going to be repeatadly filled out. this is not a one time occurance.
×