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
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
