Average of a column based on another column problem

[philia]

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.