Forum Discussion
MODE OF ALPHANUMERIC CHARACTERS
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
5 Replies
Just in case, if not 365 when as here Excel formula: Most frequently occurring text | Exceljet
With 365
= LET( distinct, SORT(UNIQUE(alphanum)), freq, COUNTIFS(alphanum,distinct), FILTER(distinct, freq=MAX(freq)))- 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.
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 )- A nice piece of lateral thinking using XMATCH to turn alphanumeric strings to unique numbers.
