MODE OF ALPHANUMERIC CHARACTERS

[SMargan]

To Everyone,

I know how to find the commonest number in a set of entries that are all numbers, i.e. the MODE function. However, I am not sure how to find the commonest letter if the entries are all letters. Does anyone know?

Simon Margan

[#2]

@SMarganHi,

You can use MATCH and count (mode) the numeric matches of the words/letters.

Combine with INDEX to get the words.

=LET(
  data;           FILTER(A:A;A:A<>"");
  numericMatches; MODE.MULT(XMATCH(data;data));
  output;         INDEX(data;numericMatches);
  output
)

[#3]

@SMargan 

With 365

= LET(
  distinct, SORT(UNIQUE(alphanum)),
  freq, COUNTIFS(alphanum,distinct),
  FILTER(distinct, freq=MAX(freq)))

[#4]

A nice piece of lateral thinking using XMATCH to turn alphanumeric strings to unique numbers.

[#5]

@SMargan 

Just in case, if not 365 when as here Excel formula: Most frequently occurring text | Exceljet

[#6]

Thank you, though I have to pass the glory to https://www.excel-easy.com/examples/mode.html :)
Your solution favours combining fewer functions into new applications - a method I generally prefer.