CODEWITHSUNDEEP
excelvbaexcel-formula
infinitedreamer666
infinitedreamer666

Reputation: 91

Excel: Find MODE of a matrix of strings

I have been trying to compute the mode of a matrix of cells (10 x 3) containing words to find the most frequent word. After online research, I could only find this formula: =INDEX(rng,MODE(MATCH(rng,rng,0))), with rng meaning the range I want to find the mode of. The problem with the formula is that rng can only be single column or single row. If I put my whole range in the formula, I get #N/A.

Is there a way to find the mode of the whole matrix? Transforming the matrix into a single column/row is not an option as the matrix is dynamic and rows keep getting added to it.

Thanks in advance!

Upvotes: 1

Views: 228

Answers (1)

JvdV
JvdV

Reputation: 75900

Try:

enter image description here

Formula in E1:

=LET(X,COUNTIF(A1:C5,A1:C5),UNIQUE(TOCOL(IF(X=MAX(X),A1:C5,NB),3)))

In case of a tie, and you want to show all, then this will spill:

enter image description here

EDIT:

If TOCOL() (which is in BETA-channel for insiders at time of writing) is not available to you, try:

=LET(X,COUNTIF(A1:C5,A1:C5),UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(X=MAX(X),A1:C5,""))&"</s></t>","//s")))

This variant will also spill down in case of a tie. Note that using TEXTJOIN() got limits but in a 10x3 array of values this will be completely fine.

If, however, you are looking for VBA, then I found an answer here that you could use as a starting point to create your own code.

Upvotes: 3

Related Questions