Multiple occurrence => 1 occurrence

[exhortae]

Hi,

I have an excel file with 2 columns (product, price)

In this file I have multiple occurrence of products :

Please Login or Register  to view this content.

I have two occurrences of beer, I want to have only one with the price being the average of the two.

Please Login or Register  to view this content.

Thanks

[NBVC]

You can use a Pivot table to list the unique items and their respective averages.

A quick start guide....

http://peltiertech.com/Excel/Pivots/pivotstart.htm

[teylyn]

Create a unique list of your products, then use Averageif() to calculate the average.

To create a unique list, you can select the Product column, then go Data - Filter - Advanced Filter, tick "unique items only" and specify to filter to a new location, select a cell for the new list.

Say your new list with unique products now is in column D, and your original data is in columns A and B. You can now use AverageIf starting in row one of a cell

=averageif($A$1:$A$100,D1,$B$1:$B$100)

copy down.

cheers

[DonkeyOte]

If as implied by your profile you are running XL2003 you can replace AVERAGEIF with a SUMIF/COUNTIF construct (AVERAGEIF introduced in XL2007)

That said - a Pivot does seem perfect...

[exhortae]

You can use a Pivot table to list the unique items and their respective averages.

A quick start guide....

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Exactly what I was looking for : A SELECT AVG GROUP BY like.

Thanks

Create a unique list of your products, then use Averageif() to calculate the average.

....
cheers

Lazy me prefers the Pivot table, but thanks for your help.

If as implied by your profile you are running XL2003 you can replace AVERAGEIF with a SUMIF/COUNTIF construct (AVERAGEIF introduced in XL2007)

That said - a Pivot does seem perfect...

I fixed my profile