Average of 1 cell depending on dependent of another cell

[bakegleeson]

Hey,
I'm trying to find the average of 1 cell that is linked to another cell.

In the image below, I am trying to get the average of the numbers linked to column A - so AAA and BBB.

Taking 'AAA' as an example, I first need to find AAA in column B and then take the letters in column C and find that in column D. In this instance, we'd be looking for ABBB and ABB2 in column D, to get the average of 15 and 15.

Same for 'BBB' - after finding BBB in column B, we're looking for BBBA, ABAB & DPDP in column D, to get the average of 10,10,10.

I think it's along the lines of if(average((A2=B2:B10)*( ........

Any takers?

 

[jasonb75]

Will your real data have duplicate pairs in B and C? In your example above, AAA and ABBB appear in 2 rows. This method works with your example but is unlikely to work with real data if you do have duplicate pairs in B & C as the corresponding values will be duplicated in the average as well. If you change the first 15 to 10 in the example, the average should be 12.5, but the formula will give you 11.25 (average of 10,10 and 15).

With office 365 it might be possible to make use of the UNIQUE / FILTER functions to clean out the duplicates, this is not something that I am able to test.

Book1
	A	B	C	D	E	F	G	H
1	A	Average		B	C		D	E
2	AAA	15		AAA	ABBB		ABBB	15
3	BBB	10		AAA	ABBB		ABBB2	15
4				AAA	ABBB2		BBBA	10
5				BBB	BBBA		ABAB	10
6				BBB	ABAB		DPDP	10
7				BBB	DPDP
8				BBB	DPDP
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=SUM(SUMIFS($H$2:$H$6,$G$2:$G$6,IF($D$2:$D$8=A2,$E$2:$E$8)))/SUM(COUNTIFS($G$2:$G$6,IF($D$2:$D$8=A2,$E$2:$E$8)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bakegleeson]

Will your real data have duplicate pairs in B and C? In your example above, AAA and ABBB appear in 2 rows. This method works with your example but is unlikely to work with real data if you do have duplicate pairs in B & C as the corresponding values will be duplicated in the average as well. If you change the first 15 to 10 in the example, the average should be 12.5, but the formula will give you 11.25 (average of 10,10 and 15).

With office 365 it might be possible to make use of the UNIQUE / FILTER functions to clean out the duplicates, this is not something that I am able to test.

Book1
	A	B	C	D	E	F	G	H
1	A	Average		B	C		D	E
2	AAA	15		AAA	ABBB		ABBB	15
3	BBB	10		AAA	ABBB		ABBB2	15
4				AAA	ABBB2		BBBA	10
5				BBB	BBBA		ABAB	10
6				BBB	ABAB		DPDP	10
7				BBB	DPDP
8				BBB	DPDP
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=SUM(SUMIFS($H$2:$H$6,$G$2:$G$6,IF($D$2:$D$8=A2,$E$2:$E$8)))/SUM(COUNTIFS($G$2:$G$6,IF($D$2:$D$8=A2,$E$2:$E$8)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks dude, that works perfectly. The formula you have is perfect, I'm actually building it for an older version of Excel, rather than 365!

 

[bakegleeson]

Will your real data have duplicate pairs in B and C? In your example above, AAA and ABBB appear in 2 rows. This method works with your example but is unlikely to work with real data if you do have duplicate pairs in B & C as the corresponding values will be duplicated in the average as well. If you change the first 15 to 10 in the example, the average should be 12.5, but the formula will give you 11.25 (average of 10,10 and 15).

With office 365 it might be possible to make use of the UNIQUE / FILTER functions to clean out the duplicates, this is not something that I am able to test.

Book1
	A	B	C	D	E	F	G	H
1	A	Average		B	C		D	E
2	AAA	15		AAA	ABBB		ABBB	15
3	BBB	10		AAA	ABBB		ABBB2	15
4				AAA	ABBB2		BBBA	10
5				BBB	BBBA		ABAB	10
6				BBB	ABAB		DPDP	10
7				BBB	DPDP
8				BBB	DPDP
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=SUM(SUMIFS($H$2:$H$6,$G$2:$G$6,IF($D$2:$D$8=A2,$E$2:$E$8)))/SUM(COUNTIFS($G$2:$G$6,IF($D$2:$D$8=A2,$E$2:$E$8)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Sorry, my bad. Yes, my data will have duplicates so it's reducing my averages. Do you have any further pointers?

 

[jasonb75]

Which older version of excel do you need it for?

First couple of attempts have failed, the older you need the less likely it will be possible.

 

[sandy666]

XL2010 / XL2019

B	Avg
AAA	15
BBB	10

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Join = Table.NestedJoin(Source1,{"C"},Source2,{"D"},"Table4",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table4", {"D", "E"}, {"Table4.D", "Table4.E"}),
    Group = Table.Group(Expand, {"B"}, {{"Avg", each List.Average([Table4.E]), type number}})
in
    Group