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

#### PolicyUser

##### New Member
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.

#### Attachments

• Excel Sample.JPG
88.3 KB · Views: 9

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Eric W

##### MrExcel MVP
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
1IDAverageQuality Measure ID 2Quality Measure Score 2Quality Measure ID 3Quality Measure Score 3Quality Measure ID 4Quality Measure Score 4Quality Measure ID 5Quality Measure Score 5
23189.886667128103189.76
39.8866673181020410
43181020410
51289.32367.52
6204103189.78
7204103189.78
8DMCOMP1020410
93181020410
10134102049.96
11134102049.96
12AC032191288.41
13
Sheet13
Cell Formulas
RangeFormula
AC2AC2=AVERAGEIF(AE2:AL20,AB2,AF2:AM20)
AC3AC3=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

##### New Member
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

##### MrExcel MVP
Happy to help! Thanks for the feedback.

Replies
10
Views
181
Replies
26
Views
492
Replies
3
Views
66
Replies
3
Views
162
Replies
3
Views
143

1,129,916
Messages
5,638,976
Members
417,063
Latest member
thematulaak

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back