Strange way of calculating chi-squared in Excel vs R

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:

2.822793
2.822793
1.759359
4.136448

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)
• The approach in your second update should give the correct statistic. However, if your expecteds are not based on the sum of the observed you could have a problem because the p-value there conditions on that. However, I notice the expected and observed have the same total (unlikely to happen by chance) so this is all probably fine. You could do it more easily this way: x=c(42,42,25,21);chisq.test(cbind(x,2000-x)) – Glen_b Feb 2 '14 at 23:58
• @Glen_b In the Excel sheet I believe the expecteds are worked out by doing Total Population * the 'P' value I worked out above. Is this going to be a problem? Also the total population does vary - most of the time it is 2000 but it could be any number really. The Excel sheet I'm trying to recreate here doesn't actually take into account the p-value, so if the statistic won't be affected by this then maybe it's not a problem... – user1578653 Feb 3 '14 at 9:17
• The question boils down to where do the p's come from. Do they involve looking at the total observed count? – Glen_b Feb 3 '14 at 10:17
• Well to me it looks like the Ps, and therefore the expected is based on both the total observed count and the total population...However in all the examples I've been given in the Excel sheet the expected value also seems to match the total observed count/number of counts. – user1578653 Feb 3 '14 at 10:59
• If the p's are based off the counts in that way, of course the expecteds follow. If that's the case, it looks like the degrees of freedom and so on are fine the way you did it in R -- but a few words of my explanation may need to change. – Glen_b Feb 3 '14 at 15:04

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.

• Thanks for your help! I'm not a mathematician/statistician so this confused me initially, but your explanation is very easy to understand. – user1578653 Jan 31 '14 at 12:15