Excel - find a non numeric average

  cedricthecat 13:49 16 Apr 2007
Locked

Or rather, the most frequently occuring word in a list, si, if I had:-

apple
banana
pear
lime
banana
grapefruit

I'd be looking to find "banana"

I'm guessing this is not hard, but I don't know how to do it, so from that point of view, it is hard!

Any advice?!

  Simsy 14:07 16 Apr 2007

make use of the "DCOUNTA" function.


From Excel Help...

********************************************

Counts all of the nonblank cells in a column in a list or database that match conditions you specify.

For more information and examples, click .

Syntax

DCOUNTA(database,field,criteria)

Database is the range of cells that make up the list or database. A database is a list of related data in which rows of related information are records and columns of data are fields. The first row of the list contains labels for each column.

Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on. If you omit field, DCOUNTA returns a count of all records that meet the criteria. If you include field, DCOUNTA returns only records that contain a value in the field and meet the criteria.

Criteria is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.


********************************************


Does this help?

Regards,

Simsy

  Simsy 14:08 16 Apr 2007

You'd then need to include the result in a "MAX" function.

Regards,

Simsy

  VoG II 14:08 16 Apr 2007

Probably the best way is to use a Pivot Table - tutorial click here

A quick and dirty method: with your data in A1:A6 in B1

=COUNTIF(A$1:A$6,A$1:A$6)

and copy the formula down.

  HappyTrucker 14:14 16 Apr 2007

You can also insert a column into your data (assuming it's existing data) and use a COUNTIF as VoG says. So, if your list is in A1 downwards, insert a column B (if it's not blank already) and insert this formula into B1:

=COUNTIF(A:A,A1) and paste this down the length of your column. This will give you the count of the number of times that the data in the adjacent cell appears in the list.

Then, somewhere on your sheet add this into a cell: =INDIRECT(ADDRESS(MATCH(MAX(B:B),B:B,0),COLUMN(A:A))) and this will give you e text value that you're looking for. In the case of your list, banana.

You can then hide column B so it doesn't mess up the appearance of your table.

  cedricthecat 14:20 16 Apr 2007

Blimey!

More complicated than I thought, but thanks for the advice!

  Woolwell 15:03 16 Apr 2007

If you don't want a cell with a calculated figure you can use Data - Filter - Autofilter which will show the number of instances of banana

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017