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

PolicyUser

New Member
Joined
Dec 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
    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
Joined
Aug 18, 2015
Messages
10,823
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
ABACADAEAFAGAHAIAJAKALAM
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.
 
Solution

PolicyUser

New Member
Joined
Dec 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top