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!
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.