averagifs of two products only when found together in same location

[nuest12]

I have been trying to figure out how to compare the averages of two corn hybrids only when they are found in the same location. Here is an example of some data

A B C

Location Hybrid Yield

1 Morris 197-68stx 215
2 Morris 203-01stx 220
3 Morris 199-29stx 200
4 Morris 205-19stx 225
5 Hancock 197-68stx 200
6 Hancock 203-01stx 190
7 Hancock 205-19stx 210
8 Benson 197-68stx 180
9 Benson 203-01stx 200
10 Willmar 197-68stx 220
11 Willmar 203-01stx 210
12 Willmar 205-19stx 215

In this case I would like to find the average of the hybrid 203-01stx and compare it to the average of the hybrid 205-19stx but only when they are both found in the same location. In the example above we would not take the average of the Benson location because the 205-19stx hybrid was not planted there. I have about 100 locations that I am trying to take averages from. It would also be nice to be able to switch the hybrids I was comparing without having to drastically change my formula. I.E. comparing 197-68stx vs. 203-01stx. I have tried a few different things such as taking the average(averageifs(c1:c12,a1:a12,a1,b1:b12,b2),averageifs(c1:c12,a1:a12,a5,b1:b12,b2),averageifs(c1:c12,a1:a12,a10,b1:b12,b2))

This gives me the average of the 203-01stx hybrid in each location it is present with the 205-19stx hybrid and then I can find the average of the 205-19stx hybrid by writing the same formula. However this requires me to go through all the data to find the locations where both hybrids are present. I am looking for a simpler and cleaner way of making this possible.

Thanks

 

[Eric W]

	A	B	C	D	E	F	G
1	Location	Hybrid	Yield
2						Hybrid1	Hybrid2
3	Morris	197-68stx	215			203-01stx	205-19stx
4	Morris	203-01stx	220
5	Morris	199-29stx	200		Averages	206.6667	216.6667
6	Morris	205-19stx	225
7	Hancock	197-68stx	200
8	Hancock	203-01stx	190
9	Hancock	205-19stx	210
10	Benson	197-68stx	180
11	Benson	203-01stx	200
12	Willmar	197-68stx	220
13	Willmar	203-01stx	210
14	Willmar	205-19stx	215

<tbody>
</tbody>

Sheet1​

F5: =AVERAGE(IF(COUNTIFS(A:A,A3:A14,B:B,$F$3)*COUNTIFS(A:A,A3:A14,B:B,$G$3),IF(B3:B14=$F$3,$C$3:$C$14)))

G5: =AVERAGE(IF(COUNTIFS(A:A,A3:A14,B:B,$F$3)*COUNTIFS(A:A,A3:A14,B:B,$G$3),IF(B3:B14=$G$3,$C$3:$C$14)))

Those are array formulas, when you enter them in the formula bar, change the references to match your sheet, then confirm with Control+Shift+Enter, not just Enter.

Let me know how they work for you.

 

[nuest12]

Works great. Thanks. You guys are so clever. Thanks for the help

 

[Eric W]

Happy to help!

 

[nuest12]

How would you do a count of how many times those hybrids are compared against each other?

 

[Eric W]

This should do it:

=SUM(IF(COUNTIFS(A:A,A3:A14,B:B,$F$3)*COUNTIFS(A:A,A3:A14,B:B,$G$3),IF(B3:B14=$F$3,1)))
with Control+Shift+Enter

 

[nuest12]

thanks again. works great.