Average if cells are duplicates

1. Average if cells are duplicates

Hi,

I have data based on zipcodes and these zipcodes fall into larger area codes. I would like to have this data as an average for each area. Basically I want all my data in new columns if areacodes match. I already have a seperate column for all the unique areacode used in my research. Presumably I would use the 'averageif' function but I do not know how to write that I want duplicates to average as a criterea. In the attachment I have made an example of what I need. What is the best way to get this average data in column 'H' and 'I' in my example?

2. Re: Average if cells are duplicates

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

How are you defining 'duplicates'? I see no duplicates in your sample.

Maybe in H2 copied across and down?

=AVERAGEIFS(C\$2:C\$10,\$B\$2:\$B\$10,\$G2)

=AVERAGEIFS(C\$2:C\$10;\$B\$2:\$B\$10;\$G2)

3. Re: Average if cells are duplicates

Thank you for your time and response. The duplicates are in column B. I would like to have the average distance (column C) and average distance (column D) for areacode A, B, and C presented in column H and I respectively.

4. Re: Average if cells are duplicates

So what was wrong with my suggestion, then? Did you try it?

Those are not duplicates, really - duplicates would be rows (records) where all columns (fields) were the same.

If my suggestion doesn't work for you, attach a workbook showing the issue.

5. Re: Average if cells are duplicates

Your suggestion works! Thank you very much and have a great day!

6. Re: Average if cells are duplicates

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

There are currently 1 users browsing this thread. (0 members and 1 guests)