MODE

[Guest]

help says i cant use the mode function on text in a table, is there an
equivalent function for text?

 

[Harlan Grove]

help says i cant use the mode function on text in a table, is there an
equivalent function for text?

If your text is in a single column, multiple row range like A1:A20, you
could use

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

I believe Leo Heuser came up with this.

 

[Jack Sons]

Harlan,

I tried, but the formula (f.i. placed in F14) gives only the word that is in
the A1 to A20 cell of the same row the formula is in (in this example A14).
If the formula is in a row other than 1 to 20 it results in #VALUE!. If
array entered it results in the word that is in A1, no matter where the
formula is placed. I thought it would result in the most occuring word in
the cells A1 to A20.

What goes wrong?

Jack Sons
The Netherlands

 

[Guest]

This is an array formula. Enter it with Control-Shift-Enter

 

[Dave Peterson]

It worked ok for me without array entering it.

This is an array formula. Enter it with Control-Shift-Enter

 

[Dave Peterson]

What did you put in A1:A20 and what formula did you use?

I'm betting a typo somewhere.

 

[Jim May]

Change all instances of A1:A20 to $A$1:$A$20 before copying.

 

[Guest]

When I first tried it I assumed (shudder) it was an array, Entered the
equation as an array and it worked. since it worked for me I assumed
(shudder) that that was the problem. thanks for the comment.

 

[Jack Sons]

Dave,

I used Harlan's formula. I now see that it won't work if there are blanks in
the range. Any way to overcome that?

Jack.

 

[Jack Sons]

Jim,

The formula need not to be copied. It is a stand alone formula, so to say.
It can be put anywhere on the spreadsheet.

Jack.

 

[Dave Peterson]

I used this:
=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))
and if the most used "value" was blank, it returned a 0.

So I used this to hide that 0:
=INDEX(A1:A20&"",MODE(MATCH(A1:A20&"",A1:A20&"",0)))

Both seemed to work ok for me.

 

[Jack Sons]

Dave,

Marvelous, thanks a lot.

Jack.

I used this:
=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))
and if the most used "value" was blank, it returned a 0.

So I used this to hide that 0:
=INDEX(A1:A20&"",MODE(MATCH(A1:A20&"",A1:A20&"",0)))

Both seemed to work ok for me.

 

[Jack Sons]

Dave,

BTW, MODE works also for a matrix, not only for 1 row or 1 column. Why can
your function only be used for 1 row or 1 column? Is it possible to let it
work for say A1:B20?

Jack.

 

[Dave Peterson]

Not by me using anything based that formula.

Dave,

BTW, MODE works also for a matrix, not only for 1 row or 1 column. Why can
your function only be used for 1 row or 1 column? Is it possible to let it
work for say A1:B20?

Jack.