Match and average similar data

[P91]

Hi

I have a sheet with many, many items of data looking a number of products and various specifications associated with it. Amongst the specs there is test data for each product, i.e. the efficiency of each product - however in my data there are duplicates of a particular product, as in the test has been run multiple times but each time it gives a different efficiency value. Example Below

Product	Efficiency	Match	Average
Red	32	1	32+33+35/3
Red	33	1	[BLANK]
Red	35	1	[BLANK]
Blue	56	2	56+57/2
Blue Stripes	63	3	63
Blue	57	2	[BLANK]

I wanted to average values if they matched names for examples (there's more criteria to be matched but if I get the gist of it, it should be easy enough to do), but I want the formula to run through the entire list, matching groups together and then averaging them based on other cell data. Ideally, I'd like to only have the average value in the first instance that product appears on the list, and leaving the rest blank.

Not sure where to start with this.

[JohnTopley]

Please post a sample file showing expected results.

[mikeTRON]

Without a sample file we can't help much, but an AverageIF formula would do just this.

https://support.office.com/en-za/art...9-f5576d8ac642
http://www.techonthenet.com/excel/fo.../averageif.php
http://www.excelfunctions.net/Excel-...-Function.html

[JohnTopley]

Data starting row 2

=IF(COUNTIF($A$2:A2,A2)=1,AVERAGEIF($A$2:$A$7,A2,$B$2:$B$7),"")

[P91]

I've added an example sheet to try and explain it a bit better. So my 'match' criteria is if the product power, motor code, test type and system code are the same then they are grouped together and I have my data sorted like that. However, I can only average them together if the the type code is the same. The averageif only works if I enter the criteria though? I have about 100 groups in my data, and about 800 rows of data.


example.xlsm

JohnTopley - If I'm reading that correctly, it will only match for the first 'case' (red), and not average the blues together?

[JohnTopley]

Much more complicated as the selection needs to be across all criteria AND you have two columns with data to be averaged. Nothing like your example!

[JohnTopley]

The attached is an example but NOT complete. You need to test if data is in AH or AI and then add repeat of formula. You might also consider a helper column to concatenate the criteria columns but it is still messy.

Results are in AW.

[JohnTopley]

A new version which uses a "helper" column in AY to group together items meeting the same criteria. The calculation (AZ) now uses this column to calculate the average. It also checks AH is blank: if so, uses AI.

You can place the helper column anywhere BUT make sure you ensure the range references are correct: some are absolute, others are relative.

If you move the helper column, place in row and then copy down as required. Ensure the cell above (row 3) is blank.

[P91]

Thanks John,

That has worked quite well, I've found that there is some inconsistency in the naming inside my data which means that it's worked for most of the data, but there's a few that have thrown it off a little. Manually adjusting them sorted that though.

Cheers again.