Excel 2008 : average values if they belong to same entry

**kmukherjee** · 12-04-2010, 05:40 PM

I'm a relatively new Excel user in Mac and a biologist. I have a dataset that has gene names in column A and associated values in column B. One gene has one or more than one associated value and i'm trying to get the average of these values for each gene. As of now i've sorted the data in column A and then manually averaged the values for each gene. But i have at least 2000 such datasets and i'm looking for a function which would do this automatically. See the attached workbook for details. Please help.

**DonkeyOte** · 12-04-2010, 05:48 PM

A PivotTable would seem the logical solution.

Highlight your Data - A & B and select Insert -> Pivot Table ... follow the default prompts. Set the PT up such that "featName" is Row Label and "AvgInt" is the Data Field - set to Average (rather than Sum)

This will give you a per item average without the need for formulae.

(If you're adamant you want formulae see AVERAGEIF function in XL Help but to reiterate a Pivot offers immense reporting flexibility for little / no effort)