Excel Macro to Perform One Sample t - test

This macro performs a single one sample t-test on an array of data. Usually the array would be one column of data, but this is not essential. The macro calculates a two-sided p-value and a confidence interval of the user's choice. Simply halve the two-sided p-value to obtain a one sided p-value (unless you're doing something very silly). If the data array is selected before you start the macro, it will appear as default input. If there is a label on the first column in the array, it will be used to label the output, whether or not it is included in the input range. Otherwise the first row label of the array will be sought. Any other column or row labels or blanks in the input range will be ignored.

Excel 2007

• Copy the file OneSampleTMacro.xls to your disk. When you first open the file you may be receive a warning that macros have been disabled. Enable the macro before proceeding.
• Enter your data into one or more columns in a worksheet of OneSampleTMacro.xls and run the macro. If your data is already in another Excel file, you can run the macro from that file provided OneSampleTMacro.xls is open with macros enabled.

Excel 95 to 2003

• Copy the file OneSampleTMacro.xls to your disk. When you first open the file you may be asked whether you want to enable macros. Do so.
• Enter your data into one or more columns in a worksheet of OneSampleTMacro.xls and run the macro. If your data is already in another Excel file, you can run the macro from that file provided OneSampleTMacro.xls is open with macros enabled.

Excel Macro to Perform One Sample Test of Proportion

This macro performs a one sample test of proportion. If the sample size does not exceed 1000, no approximations are involved in calculating the binomial probabilities (beyond whatever may be used by the Excel BINOMDIST function itself). The confidence intervals are obtained by the Excel Solver routine, finding the population proportions for which the p-value equates to the required upper and lower levels. If the sample size exceeds 1000, a Normal approximation (with continuity correction) is used to calculate the binomial probabilities in the p-value calculations. (Excel's BINOMDIST function can fail once the sample size exceeds 1000.) This is not the usual Normal approximation used in the calculation of confidence intervals, where the standard deviation is approximated using the sample proportion. Confidence intervals will never extend beyond 0 or 1 using the Solver approach.

Input is either raw data (a column of 0's and 1's) or the number of positive cases (or the sample proportion) and the sample size. The macro calculates a two-sided p-value and a confidence interval of the user's choice. Simply halve the two-sided p-value to obtain a one sided p-value (unless you are doing something stupid or an introductory statistics assignment question). If a raw data array is selected before you start the macro, it will appear as default input. If you input raw data, and there is a label on the first column in the array, it will be used to label the output, whether or not it is included in the input range. Otherwise the first row label of the array will be sought. Any other column or row labels or blanks in the input range will be ignored.

Excel 2007

• Copy the file TestProportionMacro.xls to your disk. When you first open the file you may be receive a warning that macros have been disabled. Enable the macro before proceeding.
• Before you use this macro, you must establish a reference to the Solver add-in. This process only has to be done once and is necessary whether or not you have already "added-in" the Solver routine to your Excel set-up. Be sure to save the file after you've completed this process so that it doesn't have to be repeated every time you open TestProportionMacro.xls.
• The macro accepts either un-aggregated data (a single column of 0's and 1's) or aggregated data (a sample proportion). In the former case, the data column must be in a worksheet before the macro is run, but in the latter case, the sample proportion or count is entered into the macro's dialogue box. Run the macro in the usual way. If your data is already in another Excel file, you can run the macro from that file provided TestProportionMacro.xls is open with macros enabled.

Excel 95 to 2003

• Copy the file TestProportionMacro.xls to your disk. When you first open the file you may be asked whether you want to enable macros. Do so.
• Before you use this macro, you must establish a reference to the Solver add-in. This process only has to be done once and is necessary whether or not you have already "added-in" the Solver routine to your Excel set-up. Be sure to save the file after you've completed this process so that it doesn't have to be repeated every time you open TestProportionMacro.xls.
• The macro accepts either un-aggregated data (a single column of 0's and 1's) or aggregated data (a sample proportion). In the former case, the data column must be in a worksheet before the macro is run, but in the latter case, the sample proportion or count is entered into the macro's dialogue box. Run the macro in the usual way. If your data is already in another Excel file, you can run the macro from that file provided TestProportionMacro.xls is open with macros enabled.

Excel Macro to Perform Chi Squared Test of Equal Proportions/Independence

This Macro performs a chi squared test of proportions/independence on a two-way table, which should appear as such in the worksheet. If the table is selected before you start the macro, it will appear as default input. The input should be restricted to the body of the table only. Do not include any labels or marginal totals. The macro will calculate totals and insert them into the table margins, so create some space around your table so that this process can occur benignly. If row and column labels exist, they will be used in the output. If they don't, and there is space available, default labels will be created.

Excel 2007

• Copy the file ChiSquaredMacro.xls to your disk. When you first open the file you may be receive a warning that macros have been disabled. Enable the macro before proceeding.
• Enter a two-way table of counts into a worksheet of ChiSquaredMacro.xls and run the macro. If your data is already in another Excel file, you can run the macro from that file provided ChiSquaredMacro.xls is open with macros enabled.

Excel 95 to 2003

• Copy the file ChiSquaredMacro.xls to your disk. When you first open the file you may be asked whether you want to enable macros. Do so.
• Enter a two-way table of counts into a worksheet of ChiSquaredMacro.xls and run the macro. If your data is already in another Excel file, you can run the macro from that file provided ChiSquaredMacro.xls is open with macros enabled.

Excel Macros to Analyze Two-Way Contingency Tables

TwoWayTableMacro performs several tests of equal proportions/independence on a two-way contingency table. If the table is selected before you start the macro, it will appear as default input. The input should be restricted to the body of the table only. Do not include any labels or marginal totals. The macro will calculate totals and insert them into the table margins, so create some space around your table so that this process can occur benignly. If row and column labels exist, they will be used in the output. If they don't, and there is space available, default labels will be created.

The tests performed are the Chi-squared, Likelihood ratio and Fisher's exact test. The latter can be done "exactly" or by a Monte Carlo procedure. The exact procedure can be applied provided the degrees of freedom for the table do not exceed 25 (e.g. a 6 × 6 table) but be prepared for a long wait if the degrees of freedom exceed 8. During the wait, Excel will display all the characteristics of having crashed - displaying "Not Responding" in the Task Manager, and so forth. Do not despair. Normal transmission will be restored once the calculations are complete, or your specified time limit has elapsed, whichever comes first. If the calculations are timed out, you will be given a summary of progress to date and an option to continue.

TwoWayPivotTable performs the same tests as TwoWayTable, but the input options are more flexible. You can input two columns of raw categorical data which are yet to be cross classified, case weighted columns of categorical variables, as well as a two-way table. Data summary output, such as observed and expected values, are given in Excel pivot table form. As well as looking more professional, a pivot table is an interactive object. E.g. Rows and columns of a pivot table can be re-ordered, items appearing can be hidden, the counts can be expressed as percentages of row or column totals, charts can be easily created from the table, etc. Drive the pivot table by clicking and dragging or from the pivot table toolbar, which should become visible once the table is created.

• TwoWayTableMacro.xls - Table input only. Limited output.
• TwoWayPivotTableMacro.xls - Input via table, frequency summary or raw data. Pivot table output.

Copy the file of your choice to your disk. When you first open the file you may receive a warning that macros have been disabled. Enable the macro before proceeding. (Users of Excel 2003 or earlier may simply be offered the choice of enabling or disabling the macros.) Enter the data in a worksheet as a two-way table (or in the case of TwoWayPivotTableMacro.xls in any of the allowable forms) and run the macro. (Excel 2003 or earlier users should run the macro in the appropriate way.) If the table is selected before you run the macro, it will appear as default input. The selection should be restricted to the body of the table only. Do not include any labels or marginal totals. If your data is in already in another file, you can run either macro from that worksheet provided the macro file is also open with macros enabled.