Contingency table addin - Old version

We apologize that the rest of web page is temporarily only in Slovak language.

MS Add-in for contingency table analysis. Add-in includes functions for calculation of following statistical tests:

  1. Pearson chi-square test
  2. Cramer’s V, Pearson contingency coefficient and coefficient Phi
  3. Fisher exact test
  4. Cochran-Armitage test for linear trend
  5. Goodman-Kruskal gamma (associated p-value and 95% confidence interval)
  6. Kendall’s tau-b (associated p-value and 95% confidence interval)
  7. Kendall’s tau-c (associated p-value and 95% confidence interval)
  8. Liddell (exact McNemar) test
  9. Mantel-Haenszel test (pooled OR of several stratified 2x2 tables; p-value)
  10. Tests for single-, two independent-, and paired proportions (calculation of difference and 95% confidence interval for difference between these proportions)
  11. Point and interval estimation of odds ratio (95% CI calculation using Woolf and Cornfield method) and risk ratio
Excel function statistical test/method computed
Chi2TESTindependence

Chi2 statistics; two-sided P-value; Cramer’s V; Pearson Contingency coefficient C; coefficient Phi

FisherExactTEST one- and two-sided P-value; one- and two-sided mid-P value
RiskRatio RR (95% CI)
OddsRatio OR and 95% CI based on Woolf or Cornfield method
CochranArmitageTEST Chi2 statistics and two-sided P-value for linear trend; Chi2 statistics a two-sided P-value for departure from linear trend
cTableORDINALassoc Kendall's Tau-b, Tau-c; Goodman-Kruskall gamma and associated 95%CI and two-sided P-values
Liddell_McNemarTEST

risk ratio (95%CI); two-sided P-value

MantelHaenszel

pooled OR (95%CI); Chi2 statistics; two-sided P-value

SingleProportion proportion (95% CI)
TwoIndependentProportions proportions difference (95% CI)
PairedProportions proportions difference (95% CI)
 

 

Download:

Please note that Addin works for MS Excel version 2003 and newer only, and that Office for PC is required. Although this Addin is written in excel VBA, which is included in Excel 2011 for Mac, there are substantial number of differences between excel VBA on Windows and Mac that makes this Addin incopatible. 

Version for MS office 2010: Contingency Table (v. 2010).xlam

Version for MS office 2007, 2003: Contingency Table (v. 2007, 2003).xla

Examle data set can be dowloaded here Data sets.xlsx.

Please let us know (peter.slezak5(at)gmail.com) in case the above links don't work or any other questions.

 

Instruction video:


Calculation details:

is Pearson Chi2 test statistics, where r denotes rows and c columns; i is the number of classes of grouping in the first category (row classification); j is the number of classifications of the second category (column classification); O is an observed frequency and E is an estimated expected frequency.

Cramer’s V is

, where I and J are the numbers of rows and columns, and N is the total number of events.

Pearson's contingency coefficient is calculated as follows

Coefficient Phi is defined as

 .

Fisher's exact test: The P values are computed by considering all possible tables that could yield the observed row and column totals. The resulting P-value is the sum of P values for all the possible tables with P-value lower, or equal to that of the observed table. The mid-P value for a one-sided test is obtained by including in the tail only one half of the probability of the observed table. 

Cochran-Armitage test for linear trend: The test was proposed by Cochran [1] and Armitage [2] for analysing linear trend in k´2 contingency tables, where k levels of the first category are ordered. This way, the chi-square test for the trend is more powerful than the Pearson Chi2 test.

, where k is the number of rows, N is the grand total, R is the first column total, xi is the score for linear trend (the function automatically assigns the integer score from 1 to k), ni is the row total and ri is the frequency in the first column.

Assessment of risk: Confidence interval (95%) for the odds ratio (OR) is calculated using Woolf (logit) [3] method and the method by Cornfield [4], as described in [5] on pp. 116-119, where it was among the approximate methods, described as a method of choice. The confidence interval for risk ratio (RR) is calculated using logarithmic transformation [6,7].

 

Ordinal association: When analysing r x c contingency table with both categories being ordered, more testing power can be gained using ordinal methods, such as Goodman-Kruskal gamma, Kendall's tau-b and tau-c. The gamma statistics = (P-Q)/(P+Q), where P is the number of concordant and Q is the number of discordant pairs. The p-values and standard errors (ASE0)are calculated according to Goodman and Kruskal [8,9].

, where fij is the frequency in the i-th row and j-th column; W is the grand total; .

Kendall’s tau-b is similar to gamma, except tau -b involves a correction for ties tau-b = (P-Q)/√(DrDc). Where 

with rj as the total frequency of row i; and . The Standard error for tau-b is calculated according:  

 

Kendall's tau-c, similarly as tau-b, involves correction for ties, but it is more suitable for rectangular tables.

. Standard error for tau-c is calculated as follows , where q is defined as min (number of rows, number of columns). Test statistic Z = τc/ASE0 follows a standard normal distribution.

Liddell's test. McNemar test is traditionally used when analysing paired proportions. However, the McNemar is an approximate test only, therefore we should use its exact alternative, according to Liddell [10].

Mantel-Haenszel test. This method due to Mantel and Haenszel [11] and extended by Mantel [12] is used for estimation of the common (pooled) odds ratio and for testing whether the overall degree of association in stratified 2x2 tables is significant. Pooled OR is defined as 

If any cell count in the table is equal to zero, then a continuity correction (that means adding of the value of 0.5) is applied to each cell of this table. The confidence interval for the pooled OR is calculated using the variance formula for logarithm of OR [13], future information see also [5] pp. 250-254. The test statistic is used to test hypothesis that pooled OR is different from one. The statistic has a Chi-square distribution with one degree of freedom.

Proportions: The confidence interval for a single proportion is calculated according to the Wilson score method [14,15]. The confidence interval for the difference between two independent proportions is computed by the method combining the Wilson score intervals, which was introduced in [16]. The confidence interval for two paired proportions is computed using Newcombe’s modification of the Wilson's score-based method [14,17].

 

References:

  1. W.G. Cochran, Some methods for strengthening the common chi-squared tests, Biometrics 10 (1954) 417–451.
  2. P. Armitage, Tests for Linear Trends in Proportions and Frequencies, Biometrics 11 (1955) 375–386.
  3. B. Woolf, On estimating the relation between blood group and disease, Ann. hum. Genetics. 19 (1954)  251-253
  4. J. Cornfield, A statistical problem arising from retrospective studies. Proceedings of the 3rd Berkeley Symposium. Berkeley: University of California Press 4 (1956) 135-148.
  5. J.L. Fleiss, B. Levin, M.C. Paik, Statistical Methods for Rates and Proportions 3rd ed. Wiley (2003).
  6. D. Katz, J. Baptista, S.P. Azen, M.C. Pike, Obtaining confidence intervals for the risk ratio in cohort studies, Biometrics 34 (1978) 469-474
  7. J.A. Morris, M.J. Gardner, Epidemiological studies. In Statistics with Confidence 2nd ed. London, BMJ Publishing Group, (2000) 57-72.
  8. L.A. Goodman, W.H. Kruskal, Measures of Association for Cross Classifications. II: Further Discussion and References, J. Am. Statist. Assoc. 54 (1959) 123–163.
  9. L.A. Goodman, W.H. Kruskal, Measures of association for cross classifications, III: Approximate Sampling Theory, J. Am. Statist. Assoc. 58 (1963) 310 –364.
  10. F.D.K. Liddell, Simplified exact analysis of case-referent studies: matched pairs; dichotomous exposure, J. Epidemiol. Commun. H., 37 (1983) 82-84.
  11. N. Mantel, W. Haenszel, Statistical aspects of the analysis of data from retrospective studies of disease, J. Natl. Cancer Inst. 22 (1959) 719-748.
  12. N. Mantel, Chi-square test with one degree of freedom: extension of the Mantel-Haenszel procedure, J. Am. Statist. Assoc. 58 (1963) 690-700.
  13. J. Robins, N. Breslow, S. Greenland, Estimators of the Mantel-Haenszel variance consistent in both sparse data and large strata models, Biometrics 42 (1986) 311-323.
  14. E.B. Wilson, Probable inference, the law of succession, and statistical inference, J. Am. Statist. Assoc. 22 (1927) 209-212.
  15. R.G. Newcombe, Two-sided Confidence Intervals for the Single Proportion: Comparison of Seven Methods, Statist. Med. 17 (1998a) 857-872.
  16. R.G. Newcombe, Interval estimation for the difference between independent proportions, Statist. Med. 17 (1998b) 873-890.
  17. R.G. Newcombe, Improved confidence intervals for the difference between binomial proportions based on paired data, Statist. Med. 17 (1998c) 2635-2650.