Average columns based on intermingled criteria

[rosnorter]

Hello!
I am trying to get an average of multiple columns based on the criteria from the previous cell. These are every other one, so I am having a hard time getting it to sort out the data. I need column BS to average all of the Strength Results of the cells that have a Cure Time of 28. See screenshot below. Would anybody be able to help me out? I have tried a combination of AVERAGE, IF, AVERAGEA, etc and can't seem to get it too work.

Thanks, Terence

 

[Eric W]

Welcome to MrExcel!

Try:

=SUMPRODUCT(--(BA5:BQ5=28),--(MOD(COLUMN(BA5:BQ5)-COLUMN(BA5),2)=0),BB5:BR5)/SUMPRODUCT(--(BA5:BQ5=28),--(MOD(COLUMN(BA5:BQ5)-COLUMN(BA5),2)=0))

Edit: If the strength result column could NEVER be 28, then you could use the simpler:

=AVERAGEIF(BA5:BQ5,28,BB5:BB5)

 

[rosnorter]

Welcome to MrExcel!

Try:

=SUMPRODUCT(--(BA5:BQ5=28),--(MOD(COLUMN(BA5:BQ5)-COLUMN(BA5),2)=0),BB5:BR5)/SUMPRODUCT(--(BA5:BQ5=28),--(MOD(COLUMN(BA5:BQ5)-COLUMN(BA5),2)=0))

Edit: If the strength result column could NEVER be 28, then you could use the simpler:

=AVERAGEIF(BA5:BQ5,28,BB5:BB5)

Thanks Eric! That works perfect. Now I'll have to go through and figure out how you did that. ?