How do you find an average for two numerical categories?

[seldridge]

How do I find the average number of rooms per housing unit for this data?

1- 462
2- 643
3- 1571
4- 3535
5- 4105
6-3029
7- 2044
8-1622
9- 1968

First column of numbers is # of rooms, second column is # of housing units with corresponding number of rooms. The answer should look something like 6.3 rooms. How do I calculate this when both columns are numbers?

[Dason]

Let be the number of houses with i rooms. Then what you want is

[seldridge]

Thank you for your reply, can you please briefly explain in lamens terms? Or which function I should use in Excel? I know nothing about stats. I would really appreciate it.

[Mean Joe]

Using Excel:
In column C use the formula C1=A1*B1 <this is i*X_i in Dason's formula>
Copy down thru cell C9.

In row 10 use the formula B10=sum(B1:B9) <this is the denominator in Dason's formula>, and C10=sum(C1:C9) <this is the numerator in Dason's formula>

Then by Dason's formula, you want C10/B10

[Dason]

Mean Joe did a great job of explaining it. It's pretty much just a way of writing the mean for repeated values. For example consider the data:
1 1 1 1 2 2 3 3 3 3 4 4 4 5 5

Then the mean would be:
(1+1+1+1+2+2+3+3+3+3+4+4+4+5+5)/15
=((1+1+1+1)+(2+2)+(3+3+3+3)+(4+4+4)+(5+5))/15
=(4*1 + 2*2 + 4*3 + 3*4 + 2*5)/(4+2+4+3+2)
which is just combining the repeated values and writing it in terms of the counts of how many times a value was repeated and the value itself. So intuitively that's what the formula I gave is doing.