How to output the most common value and the number of occurrences of that value in spreadsheet?

Question

There is a column Values with a number of Strings, then show below the most common value and the number of occurrences of that value (i.e. mode of Strings). Here's an example.

+--------+
| Values |
+--------+
|   AA   |
+--------+
|   BB   |
+--------+
|   AA   |
+--------+
|   AA   |
+--------+
|   GG   |
+--------+
|   DD   |
+--------+
|   DD   |
+--------+
|        |
+-----------------+--------+
|   Most Common   |   AA   |
+-----------------+--------+
| Number of times |   03   |
+-----------------+--------+

This will be done in Google Spreadsheets! Any tips?

Answer 1

For your specific example, let that be column A, so you have A1='AA', A2='BB',...,A7='DD'.

To find the number of times the max element occurs, we want to count each unique element, then return the max count, so in a cell use the formula

=MAX(COUNTIF(A1:A7,A1:A7))

This is an ARRAY formula, so in excel you must hit Ctrl+Shift+Enter to use it. To use in google spreadsheets, surround it with ARRAYFORMULA so it becomes

=ARRAYFORMULA(MAX(COUNTIF(A1:A7,A1:A7)))

Explanation: the inside countif counts the cells of A1:A7, if they are equal to each value in A1:A7, and puts them in a list. Max returns the max value in that list.

Now, to get the actual element, we have another ARRAY formula. We can do an index/match lookup to figure out the value, so on the inside of the function, max finds the value with the greatest count, then that gets passed to an index+match function to find the value in the original list

=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0))

and so for google spreadsheets

=ARRAYFORMULA(INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0)))

you replace each instance of A1:A7 with the actual range of your data.

This post was helpful: http://www.mrexcel.com/forum/excel-questions/34530-mode-text-strings.html

Answer 2

You could create a map with a string and a counter and increment the counter on each occurrence of the string. I don't know java script but something like the following sudocode should work to count the number of occurrences:

Dictionary<string, int> _map;

foreach(cell in sheet.cells)
{
    if(_map.contains(cell.value) ==  FALSE)
    {
        _map.add(cell.value)
    {
    _map.item(cell.value) += 1 // increment number of occurrences 
}

After this you should loop to find the largest number, store its associated string and find the number associated with the string of the largest number.

Answer 3

To get this working in Google Spreadsheet the above didn't work, returning an Out of Range Error instead. I had to modify the formatting a little. This is what worked;

=index(G14:ZZ14;;(MATCH(MAX(COUNTIF(G14:ZZ14,G14:ZZ14)),COUNTIF(G14:ZZ14,G14:ZZ14),0)))

Replace the 5 references to G14:ZZ14 with your range.