| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calculate the mode for a column of text?
Hi there,
I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
=INDEX(J1:J20,MATCH(MAX(COUNTIF(J1:J20,J1:J20)),CO UNTIF(J1:J20,J1:J20),0))
This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just ENTER. Adjust the range to suit your needs. -- Gary''s Student - gsnu200795 "matt3542" wrote: Hi there, I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
Many thanks, that worked perfectly, much appreciated
Matt "Gary''s Student" wrote: =INDEX(J1:J20,MATCH(MAX(COUNTIF(J1:J20,J1:J20)),CO UNTIF(J1:J20,J1:J20),0)) This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just ENTER. Adjust the range to suit your needs. -- Gary''s Student - gsnu200795 "matt3542" wrote: Hi there, I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
very clever formula!
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
Here's another one.
If there will be no empty cells within the range. Array entered** : =INDEX(J2:J11,MODE(MATCH(J2:J11,J2:J11,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "matt3542" wrote in message ... Hi there, I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
Here's another one that *will work* with empty spaces:
=INDEX(J1:J10,MODE(MATCH(J1:J10&"",J1:J10&"",0))) Still an *array* formula. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Here's another one. If there will be no empty cells within the range. Array entered** : =INDEX(J2:J11,MODE(MATCH(J2:J11,J2:J11,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "matt3542" wrote in message ... Hi there, I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
Hi there, this one also worked even though I had a couple of empty cells
within the range, again many thanks, a great help "T. Valko" wrote: Here's another one. If there will be no empty cells within the range. Array entered** : =INDEX(J2:J11,MODE(MATCH(J2:J11,J2:J11,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "matt3542" wrote in message ... Hi there, I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate the mode for a column of text?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "matt3542" wrote in message ... Hi there, this one also worked even though I had a couple of empty cells within the range, again many thanks, a great help "T. Valko" wrote: Here's another one. If there will be no empty cells within the range. Array entered** : =INDEX(J2:J11,MODE(MATCH(J2:J11,J2:J11,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "matt3542" wrote in message ... Hi there, I am trying to determine the most frequently occurring (mode) text entry but my understanding is that the mode function is only used for numeric data? I have a large dataset which lists sickness reasons and I would like to find out the mode and return the text value to cell J163. In the example below the mode would be "vomiting", please can anyone help, would be most appreciated, thanks Matt e.g Col J Vomiting Vomiting Flu symptoms Flu symptoms Flu Stomach upset Withheld Vomiting Sickness vomiting |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mode for Text | Excel Worksheet Functions | |||
| Mode for Text | Excel Discussion (Misc queries) | |||
| Calculate mode | Excel Discussion (Misc queries) | |||
| Calculate mode based on criteria | Excel Worksheet Functions | |||
| How do I calculate the mode of a column of IP addresses? | Excel Worksheet Functions | |||
Hybrid Mode
