M
Micayla Bergen
help says i cant use the mode function on text in a table, is there an
equivalent function for text?
equivalent function for text?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Harlan Grove
Micayla Bergen said: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.
J
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
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
B
bj
This is an array formula. Enter it with Control-Shift-Enter
D
Dave Peterson
It worked ok for me without array entering it.
This is an array formula. Enter it with Control-Shift-Enter
D
Dave Peterson
What did you put in A1:A20 and what formula did you use?
I'm betting a typo somewhere.
I'm betting a typo somewhere.
J
Jim May
Change all instances of A1:A20 to $A$1:$A$20 before copying.
B
bj
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.
equation as an array and it worked. since it worked for me I assumed
(shudder) that that was the problem. thanks for the comment.
J
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.
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.
J
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.
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.
D
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.
=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.
J
Jack Sons
Dave,
Marvelous, thanks a lot.
Jack.
Marvelous, thanks a lot.
Jack.
Dave Peterson said: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.
J
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.
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.
D
Dave Peterson
Not by me using anything based that formula.
Jack said: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.