How to pull values from non-adjacent cells to calculate an average

[PolicyUser]

Hi, my apologies as I will try to explain this the best way that I can. I'm trying to look up an ID number (in number form) across a range of cells in multiple columns, then pull the corresponding score (another number) located in the adjacent column, then find the average score. So for example, I'd like to pull all cells that contain the measure ID number #318, look to the adjacent column/cell that has the score (to the right of the ID#), then get the average of all those scores. I would like to do this for all measure ID numbers. This is a huge spreadsheet with about 31K rows and the data goes across multiple columns. I have 11 columns for measure ID# and 11 corresponding score columns. Thanks in advance for any help.

 

[Eric W]

Welcome to the MrExcel forum!

If your ID numbers will never be in the same range as the scores, then you can use AVERAGEIF:

Book2
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	ID	Average		Quality Measure ID 2	Quality Measure Score 2	Quality Measure ID 3	Quality Measure Score 3	Quality Measure ID 4	Quality Measure Score 4	Quality Measure ID 5	Quality Measure Score 5
2	318	9.886667		128	10	318	9.76
3		9.886667		318	10	204	10
4				318	10	204	10
5				128	9.3	236	7.52
6				204	10	318	9.78
7				204	10	318	9.78
8				DMCOMP	10	204	10
9				318	10	204	10
10				134	10	204	9.96
11				134	10	204	9.96
12				AC0321	9	128	8.41
13
Sheet13

Cell Formulas
Range	Formula
AC2	AC2	=AVERAGEIF(AE2:AL20,AB2,AF2:AM20)
AC3	AC3	=SUMPRODUCT(AF2:AM20*(AE2:AL20=AB2)*(MOD(COLUMN(AF2:AM2)-COLUMN(AF2),2)=0))/SUMPRODUCT((AE2:AL20=AB2)*(MOD(COLUMN(AF2:AM2)-COLUMN(AF2),2)=0))

It looks like this might be the case. If not, you could use the somewhat more complicated formula.


Just as an FYI, consider using the XL2BB tool (see my signature or the link in the reply box). It makes it much easier to work with your sample data rather than having to retype everything.

 

[PolicyUser]

Thank you so much, Eric. It appears that the first option works just fine, but I'll play around with the second option, too. Thanks again!!

 

[Eric W]

Happy to help! Thanks for the feedback.