In the previous post, I was talking about a dominant, which is the most frequent number. Today, we are going to find the most frequent text.
Most frequent text
This task is a bit harder, because the MODE function that we used previously needs numbers. It means that we have to convert text into numbers. To do so, we can use the MATCH function. It means that we will be looking for our whole text in our whole text. We want the exact match (Fig. 1)
Fig. MATCH function to look for the exact match
While using the MATCH function, Excel starts calculations from the top. It means that each puppy in the range is referring to the first one on the list, which is on the first position is presented as 1 (Fig. 2)
Fig. 2 Puppy gets number 1
Looking at the second example, the first kitten is in position number 2, so each kitten will refer to the same number (Fig. 3)
Fig. 3 Kitten gets number 2
Now, that we have our text connected to numbers, we can add the MODE.MULT function to return the most frequent numbers, i.e. positions of text (Fig. 4)
=MODE.MULT(MATCH(A2:A9,A2:A9,0))
Fig. 4 MODE.MULT function to return the most frequent numbers
And we have the results (Fig. 5)
Fig. 5 Results
Since we have the positions, we can add the INDEX function to return text connected to those positions (Fig. 6)
=INDEX(A2:A9,MODE.MULT(MATCH(A2:A9,A2:A9,0)))
Fig. 6 INDEX function to return text
Just like that we have our solutions in the Dynamic Array Excel (Fig. 7)
Fig. 7 Solutions in Dynami Array
In the classic Legacy Excel, we should select more cells and use the Ctrl + Shift + Enter key combination to put our results in all cells (Fig. 8)
Fig. 8 Key combination
However, we are in DA Excel, so we have a simpler solution. At the end, we want to check what happens when the text appears only once in a range. We can see that the MODE.MULT function returns the #N/A error (Fig. 9)
Fig. 9 #N/A error
When text appears the same number of times, in our case it’s cat and dog, the MODE.MULT function will return those results (Fig. 10)
Today, we want to find the most frequent value/number.
Most frequent number
From statistical point of view, we want to find a dominant. This task is quite easy in Excel, however there are some nuances. It’s easy thanks to the MODE function. We can use it to find a single dominant. In this function, we just select the range, and Excel will quickly calculate it for us (Fig. 1)
=MODE(A2:A9)
Fig. 1 MODE function
We have to remember that while using the MODE or MODE.SNGL function, Excel will return only one number. It means that we cannot be sure that only this number is the most frequent one. There is a way out, as we also have the MODE.MULT function (Fig. 2)
=MODE.MULT(A2:A9)
Fig. 2 MODE.MULT function
After using this function, we can see that in our example there are two most frequent numbers: 5 and ‑1. The MODE function showed only the number that appeared at the beginning of the range. When I change the order of the two first numbers in the range, we can see that now the MODE.SNGL function returns ‑1. The MODE.MULT function will still return two number, however in different order (Fig. 3).
Fig. 3 Different order of numbers
When we use the MODE.SNGL function in a range where there isn’t any most frequent numbers and each number occurs only once, the function will return the #N/A error. In our range of Wages, each wage appears only once (Fig. 4)
