Chi-Squared Test of Independence Using Microsoft Excel

Overview

• Tutorial on performing a chi-squared test of independence in Excel.
• Step-by-step instructions for generating a p-value.

Example Scenario

• Sample of 200 people from a local pub:
  • 100 males
  • 100 females
  • 29 male smokers, 71 male non-smokers
  • 16 female smokers, 84 female non-smokers
• Objective: Test for association between gender and smoking status.

Steps to Perform Chi-Squared Test

Step 1: Calculate Row and Column Totals

• Use the SUM function to find totals:
  • For smokers: =SUM(...) to find 45 smokers.
  • For non-smokers: Copy formula to get 155 non-smokers.
• Calculate row totals for gender:
  • Males: =SUM(...) to confirm 100 males.
  • Females: Use similar method.
• Calculate overall total (200) using row or column sums.

Step 2: Calculate Expected Values

• Formula: ((\text{Row Total} \times \text{Column Total}) / \text{Overall Total})
• Example for male smokers:
  • = ((Row Total) * (Column Total)) / Overall Total -> Expected value = 22.5
• Repeat for all categories (e.g., female smokers, non-smokers).*

Step 3: Calculate Chi-Squared Values

• Formula: ((\text{Observed} - \text{Expected})^2 / \text{Expected})
• Example calculation for male smokers:
  • = ((Observed - Expected)^2) / Expected -> Result = 1.88
• Use Excel's fill handle to repeat for all categories.

Step 4: Calculate Chi-Squared Statistic

• Sum all chi-squared values: =SUM(...) to get a chi-squared value of 4.85.

Step 5: Calculate Degrees of Freedom

• Formula: ((\text{Number of Rows} - 1) \times (\text{Number of Columns} - 1))
• Example: ((2-1) \times (2-1) = 1)

Step 6: Determine P-Value

• Use CHISQ.DIST.RT function:
  • =CHISQ.DIST.RT(Chi-Squared Value, Degrees of Freedom)
  • P-value = 0.028

Step 7: Interpret Results

• Hypotheses:
  • Null: No association between gender and smoking status.
  • Alternative: Association exists between gender and smoking status.
• Compare p-value to alpha level (0.05):
  • If p < 0.05, reject null hypothesis.
  • Conclusion: There is an association since p = 0.028 < 0.05.

Alternative Method

• Use CHISQ.TEST function:
  • =CHISQ.TEST(Observed Range, Expected Range)
  • Verifies same p-value as manual calculation.

Conclusion

• Tutorial covered chi-squared test in Excel to determine association between variables.
• Encourages engagement through likes, comments, and subscription for more tutorials.