Need a simple search function to display most common value in a column. (with ambiguous choices)

Question

I have a very large array of data with many columns that display different outputs for the values presented. I would like to add a row above the data that will display the most common occurring value or word below.

Generally I would like to have each top of the column (right under the column label in row 1) have the most common value below. I will then use this value for various data analysis functions!

Is this possible, and if so, how? Preferably this will not require VBA, but simply a short code in the cell.

One caveat: The exact values may vary, so there is no set list where I can say "it will be one of these."

Any ideas appreciated!

Answer 1

Try a series of

=COUNTIF(A:A,"VALUE TO SEARCH")

functions if you want to stay away from VBA.

Otherwise, the best method would be to iterate through each column via VBA. With this method, you can even count the "varying" values and return the count and/or the value itself.

Answer 2

http://www.excel-easy.com/examples/most-frequently-occurring-word.html

This is a single formula you would write at the top of each column. Does not require VBA. You can replace the set range to an entire column, such as (A:A) instead of (A1:A7).

If you mean an array as in a data type, it could work differently but it depends what you're trying to do.

Answer 3

With data from A3 through A16, in A2 enter:

=INDEX($A$3:$A$16,MODE(MATCH($A$3:$A$16,$A$3:$A$16,0)))

This will work for text as well as numbers. Adjust this to match the column size.