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 wrote:

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.

"Dave Peterson" schreef in bericht
...
What did you put in A1:A20 and what formula did you use?

I'm betting a typo somewhere.

Jack Sons wrote:

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

"Harlan Grove" schreef in bericht
...
"Micayla Bergen" wrote...
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.



--

Dave Peterson

--

Dave Peterson