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