getting most reccuring data

F

Fred

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.
 
M

Max

Fred said:
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.
 
M

Max

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top