 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008getting most reccuring data
I need a formula similar to MODE function but instead of numbers only it's alphanumeric(combination of letters and numbers or sometimes letters only). The output should be the most frequently reccuring data. > I need a formula similar to MODE function but instead of numbers only it's > alphanumeric(combination of letters and numbers or sometimes letters only). > The output should be the most frequently reccuring data. A fast alternative is to create a simple pivot on that alphanumeric col, with the col header placed in both the ROW and DATA areas (it'll appear as "Count of ..." in the DATA area). That'll return both the list of unique items and their counts in the pivot. Then just choose "Sort and Top10 .." option from the pivottable menu to autosort descending the counts of each unique item, using the field: "Count of ...". The top item in the sorted list will be the mode.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Assuming data in A2:A10, you could also try something like this in say, B1, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =INDEX(A2:A10,MATCH(MAX(COUNTIF(A2:A10,A2:A10)),COUNTIF(A2:A10,A2:A10),0)) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > I need a formula similar to MODE function but instead of numbers only it's > alphanumeric(combination of letters and numbers or sometimes letters only). > The output should be the most frequently reccuring data. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.