| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
MODE
help says i cant use the mode function on text in a table, is there an
equivalent function for text? |
|
#2
|
|||
|
|||
|
"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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
This is an array formula. Enter it with Control-Shift-Enter
"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. |
|
#5
|
|||
|
|||
|
It worked ok for me without array entering it.
bj wrote: This is an array formula. Enter it with Control-Shift-Enter "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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
Change all instances of A1:A20 to $A$1:$A$20 before copying.
"Jack Sons" wrote in message ... 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. |
|
#8
|
|||
|
|||
|
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. "Dave Peterson" wrote: It worked ok for me without array entering it. bj wrote: This is an array formula. Enter it with Control-Shift-Enter "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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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. "Jim May" schreef in bericht news:Fe1ge.2863$It1.2858@lakeread02... Change all instances of A1:A20 to $A$1:$A$20 before copying. "Jack Sons" wrote in message ... 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. |
|
#11
|
|||
|
|||
|
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 |
|
#12
|
|||
|
|||
|
Dave,
Marvelous, thanks a lot. Jack. "Dave Peterson" schreef in bericht ... 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 |
|
#13
|
|||
|
|||
|
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" schreef in bericht ... 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 |
|
#14
|
|||
|
|||
|
Not by me using anything based that formula.
Jack Sons wrote: 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" schreef in bericht ... 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I turn off [Group] mode? | Excel Discussion (Misc queries) | |||
| Mode Function with Criteria | Excel Worksheet Functions | |||
| Unable to open excel sheet in Protected mode from VB 6.0 | Setting up and Configuration of Excel | |||
| Combo Box goes to edit mode even if design mode is in OFF position | Excel Discussion (Misc queries) | |||
| coverting answer from Radian mode to degree mode | Excel Worksheet Functions | |||
Linear Mode
