Using EXCEL to obtain a 95% bootstrap confidence interval for a mediation effect, ab
This word document gives details of how to obtain a bootstrap confidence interval for ab. Bootstrapping has the advantage of not assuming normality ie not requiring a large sample. An example file is given here. The spreadsheet (for complete cases upto a N of 500) also computes the Sobel, Aroian and Goodman asymptotic z-tests (see sheet 1). The input data is in sheet 1 and the bootstrap random samples are placed in sheet 2. The output includes a bootstrap output spreadsheet (Bootraw4) containing results of estimating ab from 1000 bootstrap samples. In this example the bootstrap 95% confidence interval given in the Bootraw4 spreadsheet is (-1.12, 0.42) suggesting there is no statistically significant mediation effect. This nonparametric confidence interval has limits which need to be computed manually. This is easily done by using PERCENTILE(A2:A1001,0.025) and PERCENTILE(A2:A1001,0.975) to correspond to the lower and upper limits respectively (as shown in the bootraw4 spreadsheet). A bootstrapped 95% confidence interval for a the standardized effect size, kappa-squared, as recommended by Preacher and Kelley (2011) may be computed here.
To compute the bootstrap confidence intervals in these spreadsheets you will first have to download a bootstrap macro from this website onto your PC so this can be added into the version of EXCEL on your PC (instructions are given on the website and are also given in the Word document here).
Note: You may find that that you cannot update cells in the spreadsheet with new data after you have run the bootstrap macro. This can be rectified by clicking on the office button (top left corner of spreadsheet) which has red, gold, blue and green squares on it, clicking the 'EXCEL OPTIONS' button in the bottom middle, 'FORMULAS' (second from top in left hand menu) and selecting 'AUTOMATIC EXCEPT FOR DATA TABLES' in the 'Calculation options' menu at the top of the resultant window and, finally, 'OK'.
Reference
Preacher KJ and Kelley K (2011). Effect size measures for mediation models: quantitative strategies for communciating indirect effects. Psychological Methods 16(2) 93-115.