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,201Margin of Error_Current Estimate: 3230Standard Error_Current Estimate: 1963.53 (Margin of Error_Current Estimate/1.645)
Prior Estimate: 55,055Margin of Error_Prior Estimate: 2714Standard 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.