Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting most reccuring data
"Fred" wrote:
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting most reccuring data
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)),CO UNTIF(A2:A10,A2:A10),0)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fred" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) | |||
reccuring date on spread sheet | Excel Worksheet Functions |