# 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:

syntax
`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:

ABCDEF
1Q1Q2Q3
2A506055
3B455550
4C405045
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:

ABCDEF
1Q1Q2Q3
2North303532
3South343633
4East333431
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:

ABCDEF
1Q1Q2Q3
2TV252830
4Newspaper353332
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:

ABCDEF
1JanFebMar
2Product X454043
3Product Y434245
4Product Z414443
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:

ABCDEF
1Q1Q2Q3
2Team 1384037
3Team 2404241
4Team 3424143
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

1. Remember that the CHISQ.TEST function can only process numeric data.
2. Your data range must not contain any logical values, empty cells, text, or error values.
3. Ensure the actual and expected values ranges have the exact dimensions, else Excel will return a #N/A error.
4. The CHISQ.TEST function is used when data is in a frequency table.
5. CHISQ.TEST is available in Excel 2007 and later versions.
6. To gain a deeper understanding of the test, consider learning about the Chi-Square distribution and hypothesis testing.