Solved – Strange way of calculating chi-squared in Excel vs R

chi-squared-testexcelr

I'm looking at an excel sheet which claims to be calculating the $\chi^2$, but I don't recognise this way of doing it, and I was wondering if I'm missing something.

Here is the data it is analysing:

+------------------+----------+----------+
| Total Population | Observed | Expected |
+------------------+----------+----------+
|             2000 |       42 | 32.5     |
|             2000 |       42 | 32.5     |
|             2000 |       25 | 32.5     |
|             2000 |       21 | 32.5     |
+------------------+----------+----------+

And here are the sums it does for each group in order to calculate chi square:

P = (sum of all observed)/(sum of total population) = 0.01625
A = (Observed - (Population * P)) ^2
B = Total Population * P * (1-P)
ChiSq = A/B

So for each group the $\chi^2$ is:

And the total Chi Square is: 11.54139.

However, every example I have seen of calculating the $\chi^2$ is completely different from this. I would do for each group:

chiSq = (Observed-Expected)^2 / Expected

And therefore for the example above I would get a total chi square value of 11.3538.

My question is – why in the excel sheet are they calculating $\chi^2$ in this way? Is this a recognised approach?

UPDATE

My reason for wanting to know this is that I am trying to replicate these results in the R language. I am using the chisq.test function and it is not coming out with the same number as the Excel sheet. So if anyone knows how to do this approach in R it would be very helpful!

UPDATE 2

If anyone's interested, here's how I calculated it in R:

res <- matrix(c((2000-42), 42, (2000-42), 42, (2000-25), 25, (2000-21), 21), 2, 4)
chisq.test(res)

Best Answer

This turns out to be quite straightforward.

This is clearly binomial sampling. There are two ways to look at it.

Method 1, that of the spreadsheet, it to treat the observed counts $X_i$ as $\sim \text{Bin}(N_i,p_i)$, which may be approximated as $\text{N}(\mu_i=N_i\cdot p_i,\sigma_i^2=N_i\cdot p_i(1-p_i))$. As such, $Z_i=(X_i-\mu_i)/\sigma_i$ are approximately standard normal, and the $Z$'s are independent, so (approximately) $\sum_i Z_i^2\sim \chi^2$.

(If the p's are based off observed counts, then the $Z$'s aren't independent, but it's still chi-square with one fewer degree of freedom.)

Method 2: your use of the $(O-E)^2/E$ form of chi-square also works, but it requires that you take account not only of those in the category you have labelled 'Observed' but also those not in that category:

+------------+------+-------+
| Population | In A | Not A |
+------------+------+-------+
|       2000 |   42 |  1958 |
|       2000 |   42 |  1958 |
|       2000 |   25 |  1975 |
|       2000 |   21 |  1979 |
+ -----------+------+-------+

Where the $E$'s for the first column are as you have them, and those for the second column are $N_i(1-p_i)$

... and then sum $(O-E)^2/E$ over both columns.

The two forms are algebraically equivalent. Note that $1/p + 1/(1-p) = 1/p(1-p)$. Consider the i$^{th}$ row of the chi-square:

\begin{eqnarray} \frac{(X_i - \mu_i)^2}{\sigma_i^2} &=& \frac{(X_i- N_ip_i)^2}{N_ip_i(1-p_i)}\\ &=& \frac{(X_i- N_ip_i)^2}{N_ip_i} +\frac{(X_i- N_ip_i)^2}{N_i(1-p_i)}\\ &=& \frac{(X_i- N_ip_i)^2}{N_ip_i} +\frac{(N_i-N_i+N_ip_i-X_i)^2}{N_i(1-p_i)}\\ &=& \frac{(X_i- N_ip_i)^2}{N_ip_i} +\frac{(N_i-X_i-(N_i-N_ip_i))^2}{N_i(1-p_i)}\\ &=& \frac{(X_i- N_ip_i)^2}{N_ip_i} +\frac{((N_i-X_i)-N_i(1-p_i))^2}{N_i(1-p_i)}\\ &=& \frac{(O^{(A)}_i- E^{(A)}_i)^2}{E^{(A)}_i} +\frac{(O^{(\bar A)}_i-E^{(\bar A)}_i)^2}{E^{(\bar A)}_i} \end{eqnarray}

Which means you should get the same answer both ways, up to rounding error.

Let's see:

             Observed             Expected                 (O-E)^2/E          
  Ni        A     not A          A      not A             A           not A      
 2000     42         1958      32.5     1967.5       2.776923077     0.045870394     
 2000     42         1958      32.5     1967.5       2.776923077     0.045870394     
 2000     25         1975      32.5     1967.5       1.730769231     0.028589581     
 2000     21         1979      32.5     1967.5       4.069230769     0.067217281     

                                            Sum     11.35384615      0.187547649

Chi-square = 11.353846 + 0.187548 = 11.54139

Which matches their answer.

Related Solutions

Solved – chi-squared test when variables dependent

When you say the variables are dependent, I think you mean to say that the observations are dependent. Since your contingency table shows the same response levels for row and column variables, I am guessing that you have paired polytomous data. Examples of this would be self-assessment of socio-economic status in husband-wife dyads, or presence/absence of a congenital disease in heterozygous twin-pairs.

With paired binary data, there are two types of hypotheses that one can test: rater agreement versus standard row/column dependence.

With agreement, the outcome of interest can be conceived of as a binary indicator of Yes (agree) or No (disagree), so the frequencies of the diagonal of the matrix are of interest. Testing for significance of agreement can be based on percentage agreement or Cohen's Kappa (which accounts for events that are not 50/50 on average). For polytomous responses, Cohen's Kappa has a weighted analogue that can borrow information from adjacent cells.

With dependence and binary outcomes, McNemar's paired odds ratio can be used to infer a difference in the frequency of paired responses by conditioning on the first response and measuring the second response. This has the effect of discarding concordant pairs, since they tell us nothing about the differences.

With ordinal paired-outcomes, there have been several methods developed, and none of them have been received favorably. It turns out that the paired t-test has very good power to detect a difference in "mean ordinal responses" for paired ordinal outcomes. In your case of low / intermediate / high, the ordinal coding of these values would be 0 / 1 / 2.

Solved – Exact formula Yates’ correction in R

The problem was the absolute value, as @Scortchi noted.

Yates' correction modifies the $\chi^2$ statistic for a $2\times 2$ contingency table in an effort to correct the error made by using a (continuous) $\chi^2$ distribution to approximate the (discrete) sampling distribution of the statistic.

Recall that the $\chi^2$ statistic is based on the residuals in a contingency table: the differences between the observed counts $O$ and the expectations $E$ in each cell. (The expectations do not have to be whole numbers). In fact, only the sizes of the residuals really matter, because the residuals are always squared. Yates' correction subtracts $1/2$ from the size of each residual. Thus, the original formula

$$\chi^2 = \sum_{\text{cells}} \frac{(O_\text{cell} - E_\text{cell})^2}{E_\text{cell}}$$

becomes

$$\chi^2_\text{corrected} = \sum_{\text{cells}} \frac{(|O_\text{cell} - E_\text{cell}| - 1/2)^2}{E_\text{cell}}.$$

The R code for chisq.test appears to be a little subtler. Here is the relevant section. (It is buried within some nested conditionals which are not relevant here.)

        if (correct && nrow(x) == 2L && ncol(x) == 2L) {
            YATES <- min(0.5, abs(x - E))
            if (YATES > 0) 
              METHOD <- paste(METHOD, "with Yates' continuity correction")
        }
        else YATES <- 0
        STATISTIC <- sum((abs(x - E) - YATES)^2/E)

In this code, x stores the cell counts (thus playing the role of $O$) and E is a parallel array of expected values. The outer conditional (if) assures the correction is applied only when (a) it is requested, as indicated by the logical value of correct, and (b) these counts are for a $2\times 2$ table.

The use of min replaces $1/2$ in the correction by the smallest of the absolute residuals (should any of them be smaller than $1/2$). This assures that none of the corrected absolute residuals is made any less than zero. This little nicety is not noted in the Wikipedia article. Although not the same as Yates' original proposal, it can be construed as a variation of it in which no corrected value is ever made negative:

... group the $\chi$ distribution, taking the half units of deviation from expectation as the group boundaries ... . This is equivalent to computing the values of $\chi^2$ for deviations half a unit less than the true deviations, $8$ successes, for example, being reckoned as $7\frac{1}{2}$... . This correction may be styled the correction for continuity... .

Reference

The quotation is at p. 222 of

Yates, F (1934). "Contingency table involving small numbers and the χ2 test". Supplement to the Journal of the Royal Statistical Society 1(2): 217–235.