1. Random Numbers Without Duplication (Excel worksheet macro):
Here is an Excel macro that will generate an ordered list of as many random numbers that you specify, drawn in the range you between the minimum and maximum values (inclusive) that you specify, and most importantly, free of any duplicate numbers . This is a useful -- often indispensable -- feature when you are drawing a sample from a finite population (e.g., a sample of employees to include in a survey, a sample of email addresses from a large list, etc.). To use the numbers generated by this tool, just arrange the members of the population in alphabetical or any other order, assign each member a consecutive integer number from the minimum (usually 1) to the maximum number in the population, and then select as your sample all of the members with a number corresponding to one in the list generated by this worksheet.
Download this Excel macro by clicking this link: Random Numbers without Duplication (Excel macro)
2. Tetrachoric Correlation Calculator (Excel worksheet macro):
I developed this Excel macro to bring tetrachoric correlation within easy reach of more statisticians and applied statistics users. Very few mainstream statistical software systems offer it as a standard analysis option, the exceptions being Minitab, Stata, and SAS (there is also an SPSS macro for it which I wrote in 1997). However, many who have a need to compute this statistic don't have access to these systems (the costs of all except Stata exceed $1,000), or don't have the time or patience to learn how to set up their data and how to run the required procedure.
This Excel worksheet calculator allows you to either directly enter the four cell totals for which the statistic is to be computed, or your raw data. If your raw data has not yet been dichotomized (i.e., into values of 1 or 2), it offers capability to enter the data for your two variables, set the low/high (or yes/no, true/false, etc.) cutpoints for each, and produce the dichotomized versions of the variables with the click of a button. Another button click sends the dichotomized data to the calculator page, where a final click of a button produces the tetrachoric correlation and the p-value of its difference from zero. The algorithm used is a port to VBA from Brown's AS 116 algorithm (Brown, MB. (1977) Algorithm AS 116: the tetrachoric correlation and its standard error. Applied Statistics, 26 , 343-351), which has long been recognized as producing the exact value.
Download by clicking this link: Tetrachoric Correlation Calculator (Excel macro)
3. Anderson-Darling Normality Test Calculator (Excel worksheet macro):
This test for the significance of departures from normality is more sensitive to such departures, and is less sensitive to the influence of sample size (e.g., in using the Shapiro-Wilk test, I've noticed that when sample size exceeds 100, progressively smaller differences from the statistic's maximum of 1.0 get p-values that rapidly reach extremely low values). A recent Monte Carlo study of the A-D statistic found that its average p-values for thousands of samples of sizes ranging from 5 to 5,000 remain very stable. However, SPSS does not offer this statistic. This worksheet calculator allows you to enter your data and immediately produce the A-D statistic and its p-value. This worksheet was developed by SPC for Excel and made available for public use.
Download by clicking this link: Anderson-Darling Calculator (Excel macro)
4. Goal Programming Excel Template with Worked Example
Goal programming is a method of achieving an optimal solution to a decision problem having multiple criteria which can differ both in their priority and in their relative weight within a given level of priority. It is an extremely cool method of arriving at solutions which would be impractical to achieve by trial-and-error. This Excel solution uses Excel's Solver add-in (particularly, the enhanced version in Excel 2010 which offers the evolutionary algorithm). Despite its widespread applicability, good explanations or examples of goal programming are rare if not nonexistent in textbooks or on the web. This worked example will show you all the ingredients. Note that I operationalized priority differences by differentiating adjacent priority by order of magnitude weights (i.e., ^10).
Download by clicking this link: Goal Programming Excel Example & Template
5. Automatic Histogram Generator (Excel worksheet macro)
What a pain it is to set up your bins to generate a histogram in Excel. The need to produce a histogram in Excel always seems to arise only after enough time has elapsed for me to forget how I did it the last time. This worksheet automates the process for you -- just enter the data, click a button, and voila - your histogram is created. In addition to its simplicity, this worksheet allows you to choose between three different rules for determining the number of histogram intervals: Scott's rule (which tends to produce the fewest intervals), Sturges' rule (which tends to produce a slightly higher number of intervals), and the Freeedman-Diaconis rule (which tends to produce the highest number of intervals). Although Sturges' rule is used in virtually all conventional statistical software systems, the other two rules have much more valid statistical foundations.
Download by clicking this link: Automatic Histogram Generator (Excel macro)
6. Variance Homogeneity Tests & Corrected t and F tests From Summary Data (Excel worksheet macro)
Do you ever have summary data on groups (i.e., Ns, means, and, standard deviations/variances) and would like to check their compliance with the variance homogeneity assumption and/or redo tests of group differences with corrected statistics? Sometimes articles only report such such summary data and we might suspect that borderline significance or nonsignificance conclusions might change with the application of corrected tests. I could not find a simple calculator which accepted the input of such summary data and produced the appropriate variance homogeneity test and the uncorrected and corrected t or F (i.e., one-way fixed factor ANOVA) tests, so I developed this Excel calculator to meet this need. In the case of data being entered for two groups, it computes the "folded" F-ratio of their variances to test for differences in variance. This is actually a better test than Levene's test in the two-group case. When data is entered for 3 or more groups, the Bartlett test is used to test variance homogeneity since Levene's test cannot be computed from summary data. The Bartlett test tends to be "too" powerful, if that is possible, detecting very small differences in variance as significant. It is generally recommended that an alpha of .01 instead of .05 be used in interpreting the significance of this test. You might also want to compare its result with a test I have offered as an experimental alternative to its right. I'm calling this "Kane's F" because I have not seen it proposed elsewhere in the literature (please correct me if I'm wrong). It is a generalization of the "folded" F test of the variance ratio to the multiple group (>2) case. It sums all the unique ratios for K groups (namely, [K * (K-1)]/2), defining each ratio as the larger over the smaller variance in a pair. It uses K-1 as the numerator df and the Welch-Satterthwaite df as the denominator df. The 2-group means comparison reports the independent group t-value (pooled variance) and both the uncorrected and Satterthwaite-corrected df and p-values. The 3-or-more group means comparison reports the uncorrected, Brown-Forsythe corrected, and Welch-corrected F, df, and p-values.
Download by clicking this link: Variance Homogeneity Tests & Corrected t and F tests From Summary Data (Excel macro) (Note: this tool was revised to correct some bugs on June 2, 2013.)
7. Correlation and Slope Comparator (Excel worksheet calculators)
As far as I can determine, there has not been a set of Excel worksheet calculators that correctly computes all the tests of the differences between correlations and regression coefficients within each of the categories of independent and dependent, overlapping and non-overlapping, and their combinations. This set of Excel worksheet calculators seeks to fill this gap and thereby provide an alternative to running SPSS or SAS syntax routines to obtain the needed test results and corrected values. All of the calculators contained in this set of tools work from summary data (e.g., correlations, regression coefficients and their standard errors, reliabilities). The workbook contains tabs for the following calculations:
- Dependent overlapping correlations : Tests for the significance of the difference between two correlations in the situation where the two correlations share a common variable (e.g., r1,2 and r1,3) and both correlations were computed on the same cases.
- Dependent non-overlapping correlations : Tests for the significance of the difference between two correlations in the situation where there is no variable in common between the two correlations (e.g., r1,2 and r3,4), and both correlations were computed on the same cases.
- Independent samples correlations : Tests for the significance of the difference between two correlations in the situation where each correlation was computed on a different sample of cases. [Note: The example invariably used in this case is the correlation between the same two variables in different samples (i.e., complete overlap). There potentially are hidden and as yet unexplored complications for comparisons involving 50% and zero overlap between the variables correlated in separate samples.]
- Difference from hypothesized correlation : Tests for the significance of the difference between an observed correlation and the hypothesized value of the correlation. The hypothesized value may be zero or any other value between -1.0 and +1.0.
- Difference between slopes : Tests for the significance of the difference between two (raw score) slopes (i.e., also commonly referred to as b weights or raw score regression coefficients) from a regression equation. The slopes may reference the same x and y variables in the same or different samples, or different x variables for regression equations computed for different samples. It's difficult to imagine a need to compare slopes for regressions of different y variables in either the same or different samples, but there are no indications in the literature that the computation of the pooled standard error of the difference in such slopes would be any different than in the more conventional situations.
- Disattenuation of correlation : This computes three different ways of correcting a correlation for unreliability in the variables being correlated: correcting only for unreliability in the x variable, correcting only for unreliability in the y variable, and correcting for unreliability in both of the variables.
The Correlation and Slope Comparator can be downloaded by clicking this link: The Correlation and Slope Comparator (Excel worksheet calculators)
8. Breslow-Day & Tarone Homogeneity of Odds Ratio Tests (Excel worksheet macro):
The Breslow-Day and Tarone tests are the standard methods for testing the homogeneity of odds ratios. The Tarone test is actually a modification of the Breslow-Day test which produces slightly more accurate p-values, although the difference usually does not become evident until the 3rd decimal place. These tests are very useful for the purpose of testing the significance of the difference(s) between two or more 2x2 contingency tables. This capability is indispensable whenever the comparison of 2-way contingency tables is called for. In addition, these tests are also used to test whether the tables for a set of strata meet the fundamental assumption of the Cochran–Mantel–Haenszel test of whether the common odds ratio of the strata being compared differs from 1.0. This test assumes that there are no significant differences between the strata in their odds ratios. This assumption is evaluated by means of the Breslow-Day-Tarone test.
Up to now these tests have only been available in several of the large statistical software systems (i.e., SAS, SPSS, Stata, and R). These are often too expensive and/or excessively challenging to those not well-versed in these systems, thereby rendering these tests inaccessible to many who might be interested in applying these tests in their work. The macro offered here places these tests at the disposal of anyone with access to Excel, free of charge. It is currently set up with a limit of 20 strata. If any users require the ability to test the odds ratios of more strata than this, they may contact me (Jeffrey Kane) through this website to arrange the necessary modifications.
Download this Excel macro by clicking this link: Breslow-Day and Tarone Tests (Excel macro)
9. Regression Graph Creator (Excel worksheet macro):
Suppose you have computed 2 or 3 linear regression equations, and you wish to view the plots of the regression lines. This is often useful to make a quick determination of whether the lines intersect within the range of Y values of interest. This can be done, with some work, within some of the more expensive or technically challenging statistical systems (e.g., SPSS, Stata, R), but for people without the funds or skills to access these capabilities through these high level systems, there has been no readily accessible, easy to use tool for producing such graphs. This macro responds to this need. Just fill in the information requested and your graph is generated. It also computes the line intersections and reports whether the intersections occur within the range of Y encompassed by the graph.
Note that this tool will also work with multiple linear regression models as well as with simple linear regression models. To use with multiple regression models, you will have to compute the predicted values for your models, and then compute the simple regression of the observed Y values on the predicted Y values for each model. Use the slopes, intercepts, and minimums and maximums for these models to generate the graphs (the predicted Y variable will be the X variable in these analyses).
Download this Excel macro by clicking this link: Regression Graph Creator (Excel macro)
10. Correlated Random Variable Generator (Excel worksheet macro)
This Excel macro is the only standalone tool currently available that will produce two sets of normally distributed random observations which are correlated to the precise degree (to 3 decimal places) that the user specifies. The number of pairs of observations is limited to 65,517. The user may specify the means and standard deviations of the two variables, and may limit the values of each to fall within specified upper and lower boundaries.
Download this Excel macro by clicking this link: Correlated Random Variable Generator (Excel macro)
11. Effect Size Indexes Calculator (Excel worksheet macro)
The Effect Size Indexes Calculator will compute the following effect size indexes:
For 2 Groups:
• Cohen's d
• U1 (% of non-overlap)
• U2 (% in Group 1 that exceeds the same % in Group 2)
• U3 (% of Group 1 that exceeds the median of Group 2)
• CLES (Common Language Effect Size)
• Hedges' g
• Hedges & Olkin g*
• Glass' Delta
For Multiple Groups:
• eta squared
• Partial eta squared
• Omega squared
• Partial Omega squared
• Sample f-squared (Cohen)
• Population estimated f-squared
• Squared Intraclass Correlation
To compute the effect size indexes for the difference between the means of 2 groups, the user is required to enter either the group means, SDs, and Ns or the group Ns and the t or z for the group mean difference. To compute the effect size indexes for the difference between the means of multiple groups, the user is required to enter the ANOVA components (i.e., effect and error sums of squares, degrees of freedom, total N, etc.). This Calculator is generally not applicable to repeated measures, but can be used for such data if the paired t value is entered in the 2-group Calculator, and if the proper error terms are entered for the error sums of squares in the multiple group Calculator.
Download this Excel macro by clicking this link: Effect Size Indexes Calculator (Excel macro)