What formula is needed to calculate the average adjusted gross income by zip code?

Here is an example of one zip code I'm working on: A00100 is the Adjusted Gross Income (aka AGI), agi_class is the size of the adjusted gross income. This ranges from 1 to 7:

1 = 'Under $10,000' 2 = '$10,000 under $25,000' 3 = '$25,000 under $50,000' 4 = '$50,000 under $75,000' 5 = '$75,000 under $100,000' 6 = '$100,000 under $200,000' 7 = '$200,000 or more '


"Number of Returns" is the Number of tax returns for that agi_class. mysql> select A00100,zipcode,agi_class,N1 as 'Number of Returns' from taxbyzip2008 where zipcode="10021";

+-------------+---------+-----------+-------------------+
| A00100      | zipcode | agi_class | Number of Returns |
+-------------+---------+-----------+-------------------+
| -954234     | 10021   | 1         | 3589              |
| 43243455    | 10021   | 2         | 2521              |
| 149940475   | 10021   | 3         | 3939              |
| 243853640   | 10021   | 4         | 3936              |
| 262995399   | 10021   | 5         | 3025              |
| 751195421   | 10021   | 6         | 5333              |
| 10677437299 | 10021   | 7         | 7477              |
+-------------+---------+-----------+-------------------+


I'm trying to get at the Average AGI. But the Number of Returns has to have a weight in this because the agi_class of 7 has 7477 returns while the agi_class of 2 only has 2521. If I had individual data for each return this would be easy, to add them up and divided by the number of total returns. But I only have the total AGI for each agi_class.

What math formulas are needed to solve this?

Update: I'm trying to arrive near this number of $393,870 as the Average AGI as seen here for 2008 of Zip Code 10021: http://www.melissadata.com/lookups/TaxZip.asp?Zip=10021&submit1=Submit Note: The total number of returns from Melissadata for 2008 of zip code 10021 is 29,492. While the current data for 2008 directly from the IRS website shows a total number of returns as being 29,820. In the IRS document is states that in 2011 they adjusted the data. So it could be that the Melissadata numbers are from before this adjustment. I'm just guessing why the total number of returns for the same years and zip code derived from the same source might be different. - add comment migrated from mathematica.stackexchange.comOct 3 '12 at 15:57 This question came from our site for users of Mathematica. 1 Answer Maybe I am missing something but it seems like it should simply be$\frac{\sum_{i = 1}^{7} A00100_i}{\sum_{i=1}^{7}NR_i}$- +1. In R we can compute x <- matrix(c(-954234, 3589, 43243455, 2521, 149940475, 3939, 243853640, 3936, 262995399, 3025, 751195421, 5333, 10677437299, 7477), ncol=2, byrow=TRUE); y <- apply(x, 2, sum); y[1] / y[2], producing$406,697 for the average (only 3.2% higher than expected). –  whuber Oct 3 '12 at 22:32
Thanks for your reply, Whuber. Please see my additional comment under "NOTE:" above regarding the expected number from Melissadata.com. My goal is have MySQL/PHP to the calculations on this. I'm not familiar with "R" and don't have a statistics background. MySQL has similar functions to an Excel spreadsheet. Would you be able to translate how compute your "R" example in Excel? From there I could handle getting it to MySQL/PHP. Thanks! –  Edward Oct 4 '12 at 5:29
It's easy in Excel. Put A00100 in one column. Find the sum. Put NR in another column. Find the sum. Divide sum 1 by sum 2 and you're done –  Peter Flom Oct 4 '12 at 12:06
Hi Peter. Thanks for the reply. I realize if I had access to the raw data, which in this case would be the individual tax return AVGs (which I don't, no one but the IRS does) then doing a simple average would work. But I was concerned that a simple average would not be correct since it seems like each bucket of AVGs have different weights to them. Like in my example, class 7 is a larger class, so I was expecting this should be a factor in doing the calculation to the the average AGI. By the responses here it doesn't seem like that either can be done or is needed. –  Edward Oct 12 '12 at 8:26
The A00100 is the weight. –  Peter Flom Oct 12 '12 at 10:21
add comment