Cliff:
The guidebook is a good start, but you can also use the Excel Summary File
Data Retrieval Tool if you know what table and tracts you are looking for.
The tool can provide both the estimate and the margin of error right next to
each other with the selected geography by check marking the option. The
tool is located at:
http://www2.census.gov/acs2009_5yr/summaryfile/UserTools/SummaryFileDataRetr
ievalTool.zip
See if the above tool will meet your need. The data is in Excel, so you can
do all kinds of charts and summations as you need.
Enjoy;
Lori
Software Engineer
From: Liang Long <LLong(a)camsys.com>
Reply-To: <ctpp-news(a)chrispy.net>
Date: Mon, 3 Jan 2011 15:25:56 -0500
To: <ctpp-news(a)chrispy.net>
Subject: [CTPP] Re: ctpp-news Digest, Vol 83, Issue 1
Hi, Cliff
The ACS General Guidebook has one chapter devoted to calculating MOEs for
derived estimates. You may find it helpful. I was trying to locate this
document on line, but didn't see it. Attached is my downloaded version from
a while ago.
Liang Long
Cambridge Systematics, Inc.
4800 Hampden Lane
Suite 800
Bethesda, MD 20814
tel 301 347 9141
fax 301 347 0101
FHWA 202-366-6971
e-mail llong(a)camsys.comwww.camsys.comctpp-news-request(a)chrispy.net
Sent by: ctpp-news-bounces(a)chrispy.net01/03/2011 02:39 PM
Please respond to
ctpp-news(a)chrispy.net
To
ctpp-news(a)chrispy.net
cc
Subject
ctpp-news Digest, Vol 83, Issue 1
Send ctpp-news mailing list submissions to
ctpp-news(a)chrispy.net
To subscribe or unsubscribe via the World Wide Web, visit
http://www.chrispy.net/mailman/listinfo/ctpp-news
or, via email, send a message with subject or body 'help' to
ctpp-news-request(a)chrispy.net
You can reach the person managing the list at
ctpp-news-owner(a)chrispy.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of ctpp-news digest..."
Today's Topics:
1. How to Combine Median Values for Mutliple Areas (Cook, Cliff)
2. RE: How to Combine Median Values for Mutliple Areas (Eric Kramer)
----------------------------------------------------------------------
Message: 1
Date: Mon, 3 Jan 2011 13:24:06 -0500
From: "Cook, Cliff" <ccook(a)cambridgema.gov>
Subject: [CTPP] How to Combine Median Values for Mutliple Areas
To: "ctpp-news(a)chrispy.net" <ctpp-news(a)chrispy.net>
Message-ID: <600EBD94B0166D4BADC1083702EC47C4026F6C522B(a)Mordac.City>
Content-Type: text/plain; charset="us-ascii"
I am looking for guidance on how to combine median income values for several
census tracts into a single value and associated margin of error. I recall
seeing something about this topic in an ACS document but cannot locate the
information at this time. Any help would be appreciated.
Cliff Cook
----------------------------------------------------------------------------
-
Clifford Cook
Planning Information Manager
Cambridge Community Development Dept.
344 Broadway
Cambridge, MA. 02139
617/349-4656 FAX 617/349-4669 TTY 617/349-4621
email => ccook(a)cambridgema.gov
web site => < <http://www.chrispy.net/mailman/listinfo/ctpp-news>
http://www.cambridgema.gov/~CDD/>
I listened to your email using DriveCarefully and will respond as soon as I can.
Download DriveCarefully for free at www.drivecarefully.com
Sent via BlackBerry from T-Mobile
I listened to your email using DriveCarefully and will respond as soon as I can.
Download DriveCarefully for free at www.drivecarefully.com
Sent via BlackBerry from T-Mobile
I listened to your email using DriveCarefully and will respond as soon as I can.
Download DriveCarefully for free at www.drivecarefully.com
Sent via BlackBerry from T-Mobile
I'm in a statisticians dream, but my nightmare.....
Sonny Sanders, GISP
Capital Area Metropolitan Planning Organization
City of Jefferson - Department of Community Development's Planning and Transportation Services
320 E. McCarty St.
Jefferson City, MO 65101
573-634-6439
www.jeffcitymo.org/campo
[cid:image001.jpg(a)01CBAB61.4C0D2B60]
________________________________
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Jason Gillow
Sent: Monday, January 03, 2011 3:28 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] RE: How to Combine Median Values for Multiple Areas
Hello
I had a similar issue median and average values, I had to contact the Census Bureau themselves to get an answer.
Below was my question and their answer to me.
I have attached a revised version of you table with what I think is the appropriate approximations of the MOE of means, medians and ratios. You should check to make sure I found them all.
(See attached file: Ohio county spreadsheet.xlsx)
Please note:
1. I have changed the concept of your medians. They are now the average median instead of the median of medians. I did this because it makes it easier to calculate the MOE. We get back to taking the square root of the sum of MOE^2 which is as given in the appendix you mention below. Except now we divide it by the squared number of estimates in the average. In this case 144 (or 12^2). The benefit of the shift is the MOE is much smaller than what you had initially. I have to warn you that it may be an underestimate since we have not adjusted for correlation between the estimates.
2. I noticed that the median values didn't change much with the shift and the new medians seem more in line with the distributions shown that are associated with the medians.
3. Your idea of taking the square root of the median MOE^2 is inventive,
but all you did was assign an MOE from one of the other median estimates to selected median of the medians.
4. If you are unhappy with the concept change, you can go back to taking the median of medians, but I am suspicious that it isn't the truly overall
median. As far as the median's MOE, it would be a lot of work using
linear interpolation and you probably won't get anything useful. Instead
just use the median's own MOE and not the median of the MOEs. This result does give an extremely conservative MOE so technically you cannot go wrong except it probably isn't useful.
5. Your average of the means concept was fine, but I made the same change to the MOE formula as I did for the medians.
6. I did check some of your count estimates and they seem to be calculated correctly.
Again I want you to understand that what you and I are calculating here are approximations. They maybe overestimates or underestimates. But I feel they are better than what you had. Hopefully they will be useful.
Karen E. King
Supervisory Mathematical Statistician
ACS Variance Estimation and Statistical Support Branch Decennial Statistical Studies Division U.S. Census Bureau
Phone: 301 763-1974
From: Jason G
Date: 12/30/2010 09:24 AM
Subject: RE: Regarding question about summing estimates
Hello
Both questions refer to the same matter. I work for a regional planning commission that serves 12 counties in south central Ohio. I am developing a regional profile based off American Community Survey 5-year 2005-2009 data
- profile tables. I am using the formulas referenced in http://www.census.gov/acs/www/Downloads/handbooks/ACSResearch.pdf in Appendix 3 on page A-14 under section "Calculating MOEs for Aggregated Count Data" to get the aggregate estimate and Margin of error (MOE) numbers for our region. I know this works with like "total household" category for example to get the aggregate estimate and its MOE. But the problem I have is calculating the aggregate estimate and its MOE for "Average household size", "median family income" and "Rental vacancy rate" for example. I have figured out the formulas for the aggregate estimates of these types, but I have trouble with the calculating the MOEs for these aggregate estimates for like "Average household size", "median family income" and "Rental vacancy rate" for example for the region.
Attached is my main work table in Excel format for reference. The numbers in bolded red color are for the region I serve, and gray highlighted cells are the MOEs I had trouble with.
What I need to know is the formulas for calculating the MOEs in the gray highlighted cells in my spreadsheet.
Jason Gillow
Research/Planning Specialist II
Ohio Valley Regional Development Commission
9329 St Rt 220 E, Suite A
Waverly, OH 45690
Phone: 740-947-2853 or 800-223-7491 (In Ohio)
Fax: 740-947-3468
www.ovrdc.org
Email: jgillow(a)ovrdc.org
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Cook, Cliff
Sent: Monday, January 03, 2011 1:24 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] How to Combine Median Values for Mutliple Areas
I am looking for guidance on how to combine median income values for several census tracts into a single value and associated margin of error. I recall seeing something about this topic in an ACS document but cannot locate the information at this time. Any help would be appreciated.
Cliff Cook
-----------------------------------------------------------------------------
Clifford Cook
Planning Information Manager
Cambridge Community Development Dept.
344 Broadway
Cambridge, MA. 02139
617/349-4656 FAX 617/349-4669 TTY 617/349-4621
email => ccook(a)cambridgema.gov
web site => <http://www.cambridgema.gov/~CDD/>
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
I am looking for guidance on how to combine median income values for several census tracts into a single value and associated margin of error. I recall seeing something about this topic in an ACS document but cannot locate the information at this time. Any help would be appreciated.
Cliff Cook
-----------------------------------------------------------------------------
Clifford Cook
Planning Information Manager
Cambridge Community Development Dept.
344 Broadway
Cambridge, MA. 02139
617/349-4656 FAX 617/349-4669 TTY 617/349-4621
email => ccook(a)cambridgema.gov
web site => <http://www.cambridgema.gov/~CDD/>
Hi, Cliff
Nanda and I just briefly discussed your question on the median value
calculation. We come up with two options:
1. The median income should be available for each Census Tract. You can
take total households for each census tract as weights, using the weighted
average for the median household income across these Census Tracts. Then
MOEs can be calculated using the formula of weighted mean.
2. You can combine the distribution of household incomes for each census
tract and derive a single distribution for aggregated Census tracts. Then
you can come up with a median value.
Using these methods, you will need to interpolate MOEs for the upper bound
and the lower bound.
Both these methods will not give you the real median values - they are
only ballpark figures. But you can definitely try both and see which one
is better.
Liang Long
Cambridge Systematics, Inc.
4800 Hampden Lane
Suite 800
Bethesda, MD 20814
tel 301 347 9141
fax 301 347 0101
FHWA 202-366-6971
e-mail llong(a)camsys.comwww.camsys.comctpp-news-request(a)chrispy.net
Sent by: ctpp-news-bounces(a)chrispy.net
01/03/2011 02:39 PM
Please respond to
ctpp-news(a)chrispy.net
To
ctpp-news(a)chrispy.net
cc
Subject
ctpp-news Digest, Vol 83, Issue 1
Send ctpp-news mailing list submissions to
ctpp-news(a)chrispy.net
To subscribe or unsubscribe via the World Wide Web, visit
http://www.chrispy.net/mailman/listinfo/ctpp-news
or, via email, send a message with subject or body 'help' to
ctpp-news-request(a)chrispy.net
You can reach the person managing the list at
ctpp-news-owner(a)chrispy.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of ctpp-news digest..."
Today's Topics:
1. How to Combine Median Values for Mutliple Areas (Cook, Cliff)
2. RE: How to Combine Median Values for Mutliple Areas (Eric Kramer)
----------------------------------------------------------------------
Message: 1
Date: Mon, 3 Jan 2011 13:24:06 -0500
From: "Cook, Cliff" <ccook(a)cambridgema.gov>
Subject: [CTPP] How to Combine Median Values for Mutliple Areas
To: "ctpp-news(a)chrispy.net" <ctpp-news(a)chrispy.net>
Message-ID: <600EBD94B0166D4BADC1083702EC47C4026F6C522B(a)Mordac.City>
Content-Type: text/plain; charset="us-ascii"
I am looking for guidance on how to combine median income values for
several census tracts into a single value and associated margin of error.
I recall seeing something about this topic in an ACS document but cannot
locate the information at this time. Any help would be appreciated.
Cliff Cook
-----------------------------------------------------------------------------
Clifford Cook
Planning Information Manager
Cambridge Community Development Dept.
344 Broadway
Cambridge, MA. 02139
617/349-4656 FAX 617/349-4669 TTY 617/349-4621
email => ccook(a)cambridgema.gov
web site => <http://www.cambridgema.gov/~CDD/>
Hi, Cliff
The ACS General Guidebook has one chapter devoted to calculating MOEs for
derived estimates. You may find it helpful. I was trying to locate this
document on line, but didn't see it. Attached is my downloaded version
from a while ago.
Liang Long
Cambridge Systematics, Inc.
4800 Hampden Lane
Suite 800
Bethesda, MD 20814
tel 301 347 9141
fax 301 347 0101
FHWA 202-366-6971
e-mail llong(a)camsys.comwww.camsys.comctpp-news-request(a)chrispy.net
Sent by: ctpp-news-bounces(a)chrispy.net
01/03/2011 02:39 PM
Please respond to
ctpp-news(a)chrispy.net
To
ctpp-news(a)chrispy.net
cc
Subject
ctpp-news Digest, Vol 83, Issue 1
Send ctpp-news mailing list submissions to
ctpp-news(a)chrispy.net
To subscribe or unsubscribe via the World Wide Web, visit
http://www.chrispy.net/mailman/listinfo/ctpp-news
or, via email, send a message with subject or body 'help' to
ctpp-news-request(a)chrispy.net
You can reach the person managing the list at
ctpp-news-owner(a)chrispy.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of ctpp-news digest..."
Today's Topics:
1. How to Combine Median Values for Mutliple Areas (Cook, Cliff)
2. RE: How to Combine Median Values for Mutliple Areas (Eric Kramer)
----------------------------------------------------------------------
Message: 1
Date: Mon, 3 Jan 2011 13:24:06 -0500
From: "Cook, Cliff" <ccook(a)cambridgema.gov>
Subject: [CTPP] How to Combine Median Values for Mutliple Areas
To: "ctpp-news(a)chrispy.net" <ctpp-news(a)chrispy.net>
Message-ID: <600EBD94B0166D4BADC1083702EC47C4026F6C522B(a)Mordac.City>
Content-Type: text/plain; charset="us-ascii"
I am looking for guidance on how to combine median income values for
several census tracts into a single value and associated margin of error.
I recall seeing something about this topic in an ACS document but cannot
locate the information at this time. Any help would be appreciated.
Cliff Cook
-----------------------------------------------------------------------------
Clifford Cook
Planning Information Manager
Cambridge Community Development Dept.
344 Broadway
Cambridge, MA. 02139
617/349-4656 FAX 617/349-4669 TTY 617/349-4621
email => ccook(a)cambridgema.gov
web site => <http://www.cambridgema.gov/~CDD/>
