average text value for a ranage of cells

  • Thread starter Thread starter gerald B.
  • Start date Start date
G

gerald B.

I know how to do an average value formula when using numbers. Is there a way
to do that for text?

I collect data from an online form that has some free form text areas.
Company name is one of these. The results vary even though it's the same
name. i.e. Joe's Company, JC, Joe's Company Intl, etc

I want to be able to automatically grab the most commonly used value for the
name for a report.
I also want to use this where the values are given as text but from a drop
down open. This will allow me quickly gen a report based what ever value I
enter in the field. Most common choice is "?"

--

-----------------------------
gerald J Bordonaro

[631] 944-1503 Cell
[530] 425-3309 eFax
(e-mail address removed)

www.MrBns.com
http://www.linkedin.com/in/geraldbordonaro
 
If your data is in a single column...

This was posted by Peo Sjoblom:

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

or

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

both entered with ctrl + shift & enter, the first one is shorter but returns
error if there are blank cells within the range

======
This array formula:

=INDEX(A1:A20&"",MODE(MATCH(A1:A20&"",A1:A20&"",0)))

will work if there are empty cells in A1:A20, too.

gerald B. said:
I know how to do an average value formula when using numbers. Is there a way
to do that for text?

I collect data from an online form that has some free form text areas.
Company name is one of these. The results vary even though it's the same
name. i.e. Joe's Company, JC, Joe's Company Intl, etc

I want to be able to automatically grab the most commonly used value for the
name for a report.
I also want to use this where the values are given as text but from a drop
down open. This will allow me quickly gen a report based what ever value I
enter in the field. Most common choice is "?"

--

-----------------------------
gerald J Bordonaro

[631] 944-1503 Cell
[530] 425-3309 eFax
(e-mail address removed)

www.MrBns.com
http://www.linkedin.com/in/geraldbordonaro
Click to expand...
 
Most frequently occurring text:
=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

Same results, but entered with Ctrl+Shift+Enter:
=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20),0))

HTH,
Ryan--
 
Back
Top