ACS Data Users Group

Relative novice as it relates to reviewing/analyzing ACS 5 Year data.

Timely insight needed to create a formula in MS Excel 2016 worksheet to calculate the percent change between two estimates from non-overlapping ACS 5 Year Surveys (2014 vs 2019) that also should consider the margin of error for each estimate.

The MS Excel 2016 worksheet include the following seven columns;

Confidence Level: 90%

Current Estimate: 79,201
Margin of Error_Current Estimate: 3230
Standard Error_Current Estimate: 1963.53 (Margin of Error_Current Estimate/1.645)

Prior Estimate: 55,055
Margin of Error_Prior Estimate: 2714
Standard Error_Prior Estimate: 1649.85 (Margin of Error_Current Estimate/1.645)

Usually, I will calculate the Percent Change between two numbers as (Current Estimate - Prior Estimate)/Prior Estimate or 44%

But, for this case, it appears that I am dealing more with statistics and sampling. Therefore, it appears that I need to incorporate the standard of errors for each estimate.

In essence, standardizing each estimate prior to calculating a percent change between the two estimates from non-overlapping time periods.

Any insight as to a formula within MS Excel 2016 that will calculate the percent change in such a scenario?

Thanks in advance and have a great day!

Check out page 7-8 of:

https://www2.census.gov/programs-surveys/acs/tech_docs/accuracy/2019_ACS_Accuracy_Document_Worked_Examples.pdf

Appreciate the insight.

Considering that each estimate, for the current period and for the prior period will have a standard of error computed by dividing the Margin of Error (MOE) by 1.645, but the formula displayed on page 8 of the pdf document below only display "SE."

SE(X/Y)

X being the current estimate and Y being the prior estimate. The percent change (X/Y) is reduced by multiplying by the standard of error. In essence, reducing the percent change by the standard error.

But, which standard of error calculation should be used? The one for the current estimate or the one for the prior estimate?

Instinctively, one would reasonably assume that both standard of estimates should be considered and maybe should be applied separately to each estimate first before calculating a percent change involving margin of errors.

Will try maybe, (SE_Current Estimate x Current Estimate) divided by (SE_Prior Estimate x Prior Estimate)

C = Current estimate, M_C = Margin of error on Current Estimate,

P = Previous estimate, M_P = Margin of error on Previous Estimate,

% change = (C-P) / P = (C/P) - 1

Margin of error on % change = sqrt( M_C^2 + (C/P)^2 * M_P^2 ) / P

I just plugged your numbers in:

55055 --> 79201

43.9% increase with a margin of error +/- 6.3%

The 90% confidence interval is 37.6% to 50.1%.

Thanks.

It appears that the % change coincides with the way that a % change is usually calculated --> (X1 - X0)/X0

Will review the margin of error on % change formula.

I cannot remember where I downloaded this excel program from, but it has a worksheet that will calculate percentages and their margin of error.

https://www.dropbox.com/s/n7x3ocj0pujgwn6/acs_statistics_calculator.xlsx?dl=0

Thanks for the insight.

Did click the link and it appears to only address the various confidence intervals, at 90%, 95% and 99%.

For additional context, if the current and prior estimates pertain to estimates of average earnings for a particular zip code, current estimate from the ACS 5 Year 2019 Survey and prior estimate from the ACS 5 Year 2014 ACS Survey, I need to obtain the percentage increase (or decrease) between these two estimates.

But, the margin of errors are different.

Maybe the formula should incorporate the ratio of the margin of error to the estimate and then standardize this so that there is an "apples to apples" comparison between the estimates. Then, I can divide the adjusted current estimate by the adjusted prior estimate.