Spreadsheet for calculating confidence intervals

I do most of my teaching in SPSS, often through gritted teeth, because of the blind spot in epidemiological analyses. Students are justifiably demoralised when I tell them you can only get an odds ratio and its confidence interval by clicking on the rather obscure “Cochran’s and Mantel-Haenszel statistics” option under “Crosstabs”… and you can’t get a relative risk at all, nor can you just ask for a confidence interval round a proportion. Gee, who needs these boring stats anyway? They won’t help us “increase revenue, outperform competitors, conduct research and make better decisions” [http://www-01.ibm.com/software/analytics/spss/products/statistics/ – accessed 19 September 2012].

OK, that’s enough ranting. Here’s the solution: buy Stata. But in the meantime, until the shiny CD-ROM arrives from College Station, Texas, you can download this little spreadsheet which I hand out to my students. [Link updated 25 October 2013] It gives you an approximate and an exact CI in each case, and also you can look at the Excel formulas to try and work out what’s going on ‘under the hood’.



    1. Unfortunately the confidence intervals you can get from SPSS for descriptive statistics are very limited. Means are there, but very little else. To get a 95% CI around something like a standard deviation or a proportion, you would need to write a program of your own in syntax. In Stata or R, these are achieved by single line commands. I find Stata has the simplest and most consistent grammar for getting CIs. In R they tend to be scattered about in different packages. I must admit that I have never tried bootstrapping in SPSS but it seems to be there in the menus…

    1. Andy,
      I think there is a reference to Katz in the spreadsheet, for the odds ratio. I got that from Kahn & Sempos’s book “Statistical Methods in Epidemiology”. Most authors just present these formulae without crediting the inventors but they give lots of references to different approaches.

      Nowadays, though, we tend to view them all as the same likelihood-based approach using differential calculus. You can find the estimator from the first derivative of the log-likelihood, and the standard error from the second. So a general reference based on that would be a theory textbook like Casella & Berger’s “Statistical Inference”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s