Nov 28 2021 07:03 PM
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
Nov 28 2021 10:30 PM
@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
)
Nov 28 2021 10:38 PM
With 365
= LET(
distinct, SORT(UNIQUE(alphanum)),
freq, COUNTIFS(alphanum,distinct),
FILTER(distinct, freq=MAX(freq)))
Nov 28 2021 10:57 PM
Nov 28 2021 11:15 PM
Just in case, if not 365 when as here Excel formula: Most frequently occurring text | Exceljet
Nov 29 2021 12:37 AM