This example will show you how to find the most frequently occurring word in Excel.
You can use the function FASHION (MODE) to find the most frequently occurring number. But this function only works with numbers:
=MODE(A1:A7)
=МОДА(A1:A7)
You can use the function COUNTIF (COUNTIF) to count the number of occurrences of each word. But after all, we need one single formula that will return the most frequently occurring word (in our example, this is the word “circle”).
=COUNTIF($A$1:$A$7,C2)
=СЧЁТЕСЛИ($A$1:$A$7;C2)
To find the most frequently occurring word, follow the instructions below:
- Function MATCH (MATCH) returns the position of a value within a given range.
=MATCH(A7,A1:A7,0)=ПОИСКПОЗ(A7;A1:A7;0)
Explanation: The word “circle” (A7) was found in position 2 of the range A1: A7. Zero in the third argument returns an exact match.
- To find the position of the most frequently occurring words, add a function FASHION (FASHION) and replace A7 on A1: A7.
=MODE(MATCH(A1:A7,A1:A7,0))=МОДА(ПОИСКПОЗ(A1:A7;A1:A7;0)) - Let’s finish with a click Ctrl + Shift + Enter.
Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.
- Explanation:
- Range (array of constants) created with a function MATCH (MATCH) is stored in Excel memory, not in worksheet cells.
- The array of constants looks like this: {1;2;2;2;5;5;2}. The numbers show that the word “triangle” is found in position 1, “circle” in position 2, “circle” in position 2, and so on.
- This array of constants is used as an argument to the function FASHION (MODA), giving a result of 2 (the position of the most frequently occurring word).
- Use this result and function INDEX (INDEX) to return the second word from the range A1: A7, as the most common.
=INDEX(A1:A7,MODE(MATCH(A1:A7,A1:A7,0)))=ИНДЕКС(A1:A7;МОДА(ПОИСКПОЗ(A1:A7;A1:A7;0)))
