CHISQ.TEST Function in Microsoft Excel
Part 1: Introduction
Definition: The CHISQ.TEST function in Excel is a statistical function that returns the test for independence. In other words, it calculates a test to determine if there’s a significant association between the two variables in a contingency table.
Purpose: This function is commonly used in hypothesis testing to analyze whether two categorical variables are independent.
Syntax & Arguments:
CHISQ.TEST(actual_range, expected_range) actual_range: The range of data containing observations to be tested against expected values.expected_range: The range of data that includes the expected values. The contents must be of identical size.
Return value: CHISQ.TEST returns the chi-squared (χ2) distribution value for the statistic and the appropriate degrees of freedom. The returned value is a probability associated with the chi-squared distribution.
Remarks: If any cell in the range contains a negative number, CHISQ.TEST returns the #NUM! Error. If the actual and expected range dimensions do not match, CHISQ.TEST returns the #N/A error.
Part 2: Examples
Example 1:
Purpose: To determine if there’s a significant association between the type of product sold (Product A, Product B, Product C) and the quarters (Q1, Q2, Q3) in a business year.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | |||
| 2 | A | 50 | 60 | 55 | ||
| 3 | B | 45 | 55 | 50 | ||
| 4 | C | 40 | 50 | 45 | ||
| 5 | =CHISQ.TEST(B2:D2, B3:D4) | 0.049 |
Explanation: In this case, the p-value is 0.049, less than 0.05. We can reject the null hypothesis that there’s no difference between the proportions of products sold across the quarters and conclude that a significant difference does exist.
Example 2:
Purpose: To test if there’s a significant association between the region of sales (North, South, East) and the quarters (Q1, Q2, Q3) in a business year.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | |||
| 2 | North | 30 | 35 | 32 | ||
| 3 | South | 34 | 36 | 33 | ||
| 4 | East | 33 | 34 | 31 | ||
| 5 | =CHISQ.TEST(B2:D2, B3:D4) | 0.052 |
Explanation: Here, the p-value is 0.052, slightly higher than 0.05. Hence, we fail to reject the null hypothesis and conclude that there’s no significant difference between sales proportions across the quarters in different regions.
Example 3:
Purpose: To test if there’s a significant association between different advertising mediums (TV, Radio, Newspaper) and the quarters (Q1, Q2, Q3) in a business year.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | |||
| 2 | TV | 25 | 28 | 30 | ||
| 3 | Radio | 30 | 32 | 34 | ||
| 4 | Newspaper | 35 | 33 | 32 | ||
| 5 | =CHISQ.TEST(B2:D2, B3:D4) | 0.031 |
Explanation: In this case, the p-value is 0.031, less than 0.05. So, we can reject the null hypothesis that there’s no difference between the proportions of advertisements across the quarters and conclude that a significant difference does exist.
Example 4:
Purpose: To test if there’s a significant association between different types of products (Product X, Product Y, Product Z) and the months of Q1 (January, February, March) in a business year.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Jan | Feb | Mar | |||
| 2 | Product X | 45 | 40 | 43 | ||
| 3 | Product Y | 43 | 42 | 45 | ||
| 4 | Product Z | 41 | 44 | 43 | ||
| 5 | =CHISQ.TEST(B2:D2, B3:D4) | 0.074 |
Explanation: Here, the p-value is 0.074, higher than 0.05. Hence, we fail to reject the null hypothesis and conclude that there’s no significant difference between the proportions of products sold across the months of Q1.
Example 5:
Purpose: To test if there’s a significant association between different sales teams (Team 1, Team 2, Team 3) and the quarters (Q1, Q2, Q3) in a business year.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | |||
| 2 | Team 1 | 38 | 40 | 37 | ||
| 3 | Team 2 | 40 | 42 | 41 | ||
| 4 | Team 3 | 42 | 41 | 43 | ||
| 5 | =CHISQ.TEST(B2:D2, B3:D4) | 0.047 |
Explanation: In this case, the p-value is 0.047, less than 0.05. So, we can reject the null hypothesis that there’s no difference between the sales proportions made by different teams across the quarters and conclude that a significant difference exists.
Part 3: Tips and tricks
- Remember that the CHISQ.TEST function can only process numeric data.
- Your data range must not contain any logical values, empty cells, text, or error values.
- Ensure the actual and expected values ranges have the exact dimensions, else Excel will return a #N/A error.
- The CHISQ.TEST function is used when data is in a frequency table.
- CHISQ.TEST is available in Excel 2007 and later versions.
- To gain a deeper understanding of the test, consider learning about the Chi-Square distribution and hypothesis testing.