Monday, May 17, 2010

95 Percent Confidence Interval for Proportions in Excel

I worked  this out a while ago because I needed to do these for graphs in my masters thesis.  If anyone can use them go for it, although I would appreciate being credited somehow if you use them in a publication of any sort.
Bear in mind I am an archaeologist by trade and make no claim to these being absolutely correct.  If anyone notices a problem with them please let me know!  They seem to work pretty well.
95 Percent Confidence Interval for Proportions Formula for Excel

=((SQRT(((1-(U3/$U$7))*(1-(1-(U3/$U$7)))/U3))*1.96)*100)

This works where the cell defined as U3 = n (# of Xs in a Sample) and $U$7 = N (Total sample size).  The static reference allows the formula to be copied down a column and keep the total sample size fixed in all calculations.  The non-static (U3) reference will update (in excel) down the column to use the value from the appropriate (row-adjacent) n value.

The formula is derived as follows: (Formula values taken from spreadsheets that PTM built, I just spread them out and made them excel friendly.

1-p = 1-(n/N)
q = 1-p = 1-(1-(n/N)
p*q = (1-(n/N)) * (1-(1-(n/N))
(p*q)/n = ((1-(n/N)) * (1-(1-(n/N)))/n
sqrt((p*q)/n) = sqrt(((1-(n/N)) * (1-(1-(n/N)))/n)
Z(sqrt((p*q)/n)) = (sqrt(((1-(n/N)) * (1-(1-(n/N)))/n))*1.96
95% CI = Z(sqrt((p*q)/n))*100 = ((sqrt(((1-(n/N)) * (1-(1-(n/N)))/n))*1.96)*100

This final formula was copied and pasted into an excel cell, and made into a formula by pre-pending an equals sign to the front, yielding:

=((SQRT(((1-(n/N))*(1-(1-(n/N)))/n))*1.96)*100)

The n and N values were then replaced with the appropriate cell references, yielding:

=((SQRT(((1-(U3/$U$7))*(1-(1-(U3/$U$7)))/U3))*1.96)*100)

The final formula.

The values calculated by the final formula were cross checked against the values my thesis advisor derived by calculating the value across several columns and they match up perfectly.  I suggest that the calculated CI values be rounded to the nearest whole integer because it makes them easier to see.

No comments:

Post a Comment

Post a Comment