# Average of a column based on another column problem

#### philia

##### New Member
Yesterday I posted a question related to this problem but I'm posting it as a new question, as I’m asking something different about the problem.

I have two columns of numbers: Columna A and Column B. Column A contains a rank scoring of a population sample, whose scored counts are in Column B. So if Column A had a 1 and Column B had 234, that would mean 234 people scored a 1. I want to know where on average the people in Column B score according to the scale in Column A.

Here is where I’m having trouble: In my data, Column A is given to me as a range, of 5 points, so that cell a1 = 0-5, a2 = 6-10, and so on up to a20 = 96-100. How can I go about calculating the average score of Column B with respect to Column A? I’ve been tempted to simply make a1 = 2.5, a2 = 7.5, and so forth—but I don’t believe that’s valid (is it?) and, furthermore, I also know that the population in Column B corresponding with a score of 1 and 2, consolidated in the Column A 0-5 range of cell a1, is much larger than the population in Column B corresponding with a score of 99 and 100 which would b consolidated in the Column A 96-100 range of cell a20.

With a simple 1-10 score in Column A, I would multiply Column A by Column B in Column C, and then sum Column C and divide the sum of Column C with the sum of Column B…and I’m not even certain that’s correct. Anyway, I’ll give a sample of the numbers below:

Column A (the scale is not actually 1-10 but is 1-100 in increments of 5):

a1 = 0-5
a2 = 6-10
a3 = 11-15
a4 = 16-20
a5 = 21-25

If somebody wants to copy-and-paste this into a spreadsheet:

0-5
6-10
11-15
16-20
21-25

Column B:

b1 = 1391
b2 = 1445
b3 = 2282
b4 =1255
b5 = 1710

Again, if somebody wants to copy-and-paste this into a spreadsheet:

1391
1445
2282
1255
1710

Any advice is welcome. Thank you.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Replies
10
Views
292
Replies
3
Views
134
Replies
13
Views
234
Replies
3
Views
267
Replies
2
Views
176

Threads
1,206,760
Messages
6,074,779
Members
446,087
Latest member
PinkFloyd

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

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