Use AverageIFS to find data in a certain column before taking the average of another column

[Jason12345]

Hi

I have a set of data conduit 1,2,3

I need to search across each conduit number to see if a particular conduit is there. If the conduit is found in either spot. it needs to then take the average of the the Cable Area.
Note: there are three categories due to cables running through multiple conduits so the conduits have been split up.
Note. there will be instances where the same number for example 8 will be displayed in all three conduits.

On a new sheet I have tried the following:

Where is says average:
=AVERAGE(IF('20175858_A2_DEVICE'!I:I='Conduit ratio'!B42,'20175858_A2_DEVICE'!$L:$L,""),IF('20175858_A2_DEVICE'!J:J='Conduit ratio'!B42,'20175858_A2_DEVICE'!$L:$L,""),IF('20175858_A2_DEVICE'!K:K='Conduit ratio'!B42,'20175858_A2_DEVICE'!$L:$L,""))

It goes to the previous page to find the conduit location and and searches for the number and then takes the average across them. How ever it has come back incorrect given the the correct answer is 1.98745 and NOT 1.8947

My second attempt was to do AVERAGEIFS:
=AVERAGEIFS('20175858_A2_DEVICE'!L156:L5357,'20175858_A2_DEVICE'!I:I,'Conduit ratio'!G42,'20175858_A2_DEVICE'!J:J,'Conduit ratio'!G42,'20175858_A2_DEVICE'!K:K,'Conduit ratio'!G42)
However when doing average ifs where the cable Area is the average condition and then setting each conduit column as the criteria but I keep getting

Not sure where I am going wrong or if what I am trying to achieve is possible.

 

[Eric W]

Welcome to the MrExcel forum!

It's hard to tell exactly what you want, since you're not showing all of your data, and what you do have doesn't show the rows/columns so that we can see how they relate to your formulas. There is a tool here called XL2BB which helps you display that. See the link in my signature or in the reply box. I use it below.

Based on your description, maybe something like this:

Book3
	A	B	C	D	E	F	G	H
1	Conduit 1	Conduit 2	Conduit 3	Cable Area mm^2			Conduit
2	1		3	10			1	11.33333
3	2	1		11			2	13.5
4	3			12			3	11
5		5	1	13			5	13.5
6		7	5	14			7	14
7				15			9	#CALC!
8		2		16
9				17
10
Sheet4

Cell Formulas
Range	Formula
H2:H7	H2	=AVERAGE(FILTER($D$2:$D$10,($A$2:$A$10=G2)+($B$2:$B$10=G2)+($C$2:$C$10=G2)))

 

[Jason12345]

Welcome to the MrExcel forum!

It's hard to tell exactly what you want, since you're not showing all of your data, and what you do have doesn't show the rows/columns so that we can see how they relate to your formulas. There is a tool here called XL2BB which helps you display that. See the link in my signature or in the reply box. I use it below.

Based on your description, maybe something like this:

Book3
	A	B	C	D	E	F	G	H
1	Conduit 1	Conduit 2	Conduit 3	Cable Area mm^2			Conduit
2	1		3	10			1	11.33333
3	2	1		11			2	13.5
4	3			12			3	11
5		5	1	13			5	13.5
6		7	5	14			7	14
7				15			9	#CALC!
8		2		16
9				17
10
Sheet4

Cell Formulas
Range	Formula
H2:H7	H2	=AVERAGE(FILTER($D$2:$D$10,($A$2:$A$10=G2)+($B$2:$B$10=G2)+($C$2:$C$10=G2)))

That worked. Thanks so much. Its an interesting way of doing it. I thought of a similar process but couldnt quite get it right. Thanks for your assistance.

 

[Eric W]

Happy to help! ?