MODE OF ALPHANUMERIC CHARACTERS

%3CLINGO-SUB%20id%3D%22lingo-sub-3012747%22%20slang%3D%22en-US%22%3EMODE%20OF%20ALPHANUMERIC%20CHARACTERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012747%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20find%20the%20commonest%20number%20in%20a%20set%20of%20entries%20that%20are%20all%20numbers%2C%20i.e.%20the%20MODE%20function.%20However%2C%20I%20am%20not%20sure%20how%20to%20find%20the%20commonest%20letter%20if%20the%20entries%20are%20all%20letters.%20Does%20anyone%20know%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimon%20Margan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3012747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3013043%22%20slang%3D%22en-US%22%3ERe%3A%20MODE%20OF%20ALPHANUMERIC%20CHARACTERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3013043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1231948%22%20target%3D%22_blank%22%3E%40SMargan%3C%2FA%3EHi%2C%3C%2FP%3E%3CP%3EYou%20can%20use%20MATCH%20and%20count%20(mode)%20the%20numeric%20matches%20of%20the%20words%2Fletters.%3C%2FP%3E%3CP%3ECombine%20with%20INDEX%20to%20get%20the%20words.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DLET(%0A%20%20data%3B%20%20%20%20%20%20%20%20%20%20%20FILTER(A%3AA%3BA%3AA%26lt%3B%26gt%3B%22%22)%3B%0A%20%20numericMatches%3B%20MODE.MULT(XMATCH(data%3Bdata))%3B%0A%20%20output%3B%20%20%20%20%20%20%20%20%20INDEX(data%3BnumericMatches)%3B%0A%20%20output%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_0-1638167304571.png%22%20style%3D%22width%3A%20201px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329924i62FDE8D180391D11%2Fimage-dimensions%2F201x142%3Fv%3Dv2%22%20width%3D%22201%22%20height%3D%22142%22%20role%3D%22button%22%20title%3D%22bosinander_0-1638167304571.png%22%20alt%3D%22bosinander_0-1638167304571.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3013045%22%20slang%3D%22en-US%22%3ERe%3A%20MODE%20OF%20ALPHANUMERIC%20CHARACTERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3013045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1231948%22%20target%3D%22_blank%22%3E%40SMargan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20365%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20distinct%2C%20SORT(UNIQUE(alphanum))%2C%0A%20%20freq%2C%20COUNTIFS(alphanum%2Cdistinct)%2C%0A%20%20FILTER(distinct%2C%20freq%3DMAX(freq)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

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

bosinander_0-1638167304571.png

@SMargan 

With 365

= LET(
  distinct, SORT(UNIQUE(alphanum)),
  freq, COUNTIFS(alphanum,distinct),
  FILTER(distinct, freq=MAX(freq)))
A nice piece of lateral thinking using XMATCH to turn alphanumeric strings to unique numbers.
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.