Statistics for everyone: Excel and Statistics(25), Chi-square test

Excel and Statistics, everybody should know. : Contents

Chi-square test

What is unfortunately missing from Excel’s ‘Data Analysis’ is the chi-square test. But in fact, it is easily calculated in Excel.

	Success	Fail
Treat A	40	60
Treat B	66	34

Patients who were randomly divided into two Treatment groups result as shown in the table above. Can it be said that the Success rate varies depending on the Treatment? The Success rate is 40% and 66%, which seems quite different.

Visit https://goo.gl/9K07 of and enter the above 4 numbers into observed (yellow field). Expected and the rest are the values required in the calculation process and are calculated by themselves.

At the bottom, Karl Pearson’s p-value is the commonly referred to p-value of chi-square, and Frank Yates’ is a ‘continuity-corrected p-value’. Egon Pearson is the son of Karl Pearson, and his p-value is not utilized much.

It’s entirely an Excel file, and if you move it to the right, it gives you the OR, RR, RD, NNT and its 95% confidence intervals. SPSS doesn’t get them very well, so I made it in Excel and released it on the Internet. (OR= odds ratio, RR=risk ratio, RD=risk difference, NNT=number needed to treat)

Further to the right, you will save Cramer’s V and Phi φ.

If you go downwards, we’ve presented a bar graph to match this data, so you should be able to visualize it easily. At the bottom right, there is an icon (red arrow) where you can download this Excel file, so you can download it if you want.

Visit https://tinyurl.com/X2simple and enter the numbers aboveŒ. The (Pearson’s) p-value and (Yates’) continuity-corrected p-value are calculated. You can also download and use itŽ. The value is the same as in the previous case.

If the result is 3 instead of 2, more cells will be needed horizontally and vertically. A total of 8×8 is possible. If it is greater than 2×2, the continuity corrected p value cannot be calculated. In many cases, it is 2×2.

Plus Alpha

Let’s open the ‘pivot-chi-square test.xlsx’ file. After specifyingŒ a cell inside the data, let’s click <PivotTable> in the <Insert>.

Naturally, a dotted line appears that envelops the entire data to specify Žthe range . Click <OK>.

Drag the ‘camel contact’ that may correspond to the cause to the ‘row’ and the ‘bottle’ that may correspond to the result to the ‘column’. In fact, you can arrange rows and columns interchangeably, but I always have this rule. Also, drag ‘number’ to ‘value’, which is actually not only ‘number’, but also ‘gender ‘, ‘camel contact’, ‘illness ‘, or anything else. At this point, it is important to adjust it so that it appears as a ‘count’. The result is shown as 2 by 2 table.

Visit https://goo.gl/9K07 of and enter the yellow field in the observed field as appropriate. Shows the statistical test and p-value of Pearson chi-square and the statistical test and p-value of chi-square with Yates’ continuity correction‘. As you can see here, the former is always smaller than the latter, so there is a good chance that it will come out significantly.

On the other hand, Pearson chi-square basically calculated on the premise of a large population with a very large number of yellow cells, and if the number becomes small, it is preferable to run a Fisher exact test based on the premise of a small group, and in the past, the Fisher exact test also shows the statistical test and p-value of the chi-square with Yates’ continuity modification, which makes it close to the Fisher exact test with a simple calculation because the calculation is complex, and the p-value of the Fisher exact test is ’also Let me calculate. The Fisher exact test is essentially a one-tailed test, but it is usually a two-tailed test that approximates multiplied by 2.

As explained earlier, it is recommended to express odds ratio (OR), risk ratio (RR), risk difference (RD), or NNT, etc., and it is easier to understand if it is expressed as a graph.

Drag ‘Camel Contact’ to pull it out, and drag ‘Camel Milk Intake’ to ‘Row’ to insert it. A 2 by 2 table is created immediately. As you can see in this case, when you replace r aw data with 2 by 2 table, it is very easy to use a pivot table in Excel. Although pivot tables are powerful, we have learned the most basic way to create a table for chi-square testing.

Visit https://tinyurl.com/OR-RR-epidemiologist. All four input fields are arranged in rows (yellow) so that numerous chi-square test calculations can be performed downwards. This is a chi-square test for epidemiological investigators, and the results are tabulated that can be put directly into the report.

Data bars are good for visually comparing results, and odds ratios, risk ratios, and confidence intervals are also displayed.

It easily reduces the need to repeat chi-square tests dozens and hundreds of times at once, and it’s convenient to put them directly into a table.

At the bottom of the https://tinyurl.com/OR-RR-epidemiologist are real-world examples of various reports, which are almost identical.

Excel and Statistics, everybody should know. : Contents

=================================================