CHISQ.INV

Calculates the inverse of the left-tailed probability of the chi-squared distribution.

Syntax:

CHISQ.INV(probability, deg_freedom)

probability is required, and is the probability associated with the chi-squared distribution.

deg_freedom is required, and is the degrees of freedom of the distribution.

Example:

If probability contains 0.95 and deg_freedom contains 3:

CHISQ.INV(0.95, 3)

returns 7.814727903

This example finds the chi-square value that corresponds to the 95th percentile of the chi-squared distribution with 3 degrees of freedom.

Probability:

Deg_freedom:

Result:

7.814727903

Application:

Let's consider an application of using CHISQ.INV: A company wants to know if there's a relationship between the training method used for new employees and their performance rating after three months.

Scenario:

Training Methods (Categorical Variable 1): In-person training, Online modules, Hybrid (mix of both)
Performance Ratings (Categorical Variable 2): Below Expectations, Meets Expectations, Exceeds Expectations

The company collected data on 200 new employees and summarized the results in a contingency table.

Step 1: Formulate the Hypotheses

Null Hypothesis (H0): There is no relationship between the training method and employee performance. They are independent.
Alternative Hypothesis (Ha): There is a relationship between the training method and employee performance. They are dependent.

Step 2: Collect and Organize the Data

The observed frequencies are shown in the following table:

Observed Frequencies (O)

	Training Method	Below Expectations	Meets Expectations	Exceeds Expectations	Total
	A	B	C	D	E
1	In-person	10	45	35	90
2	Online	25	30	15	70
3	Hybrid	15	20	5	40
4	Total	50	95	55	200

Step 3: Determine the Significance Level and Degrees of Freedom

Significance Level (α): Let's choose a standard α=0.05. This means we are willing to accept a 5% chance of rejecting the null hypothesis when it's actually true.
Degrees of Freedom (df): The formula for degrees of freedom in a chi-square test is (number of rows - 1) * (number of columns - 1).
- Rows = 3 (In-person, Online, Hybrid)
- Columns = 3 (Below, Meets, Exceeds)
- df=(3−1)∗(3−1)=2∗2=4

Step 4: Use CHISQ.INV to Find the Critical Value

This is where the CHISQ.INV function comes in. We need to find the critical chi-square value that corresponds to our chosen significance level (0.05) and degrees of freedom (4).

Excel Formula: CHISQ.INV(0.05, 4)
Result: The function will return the critical value, which is approximately 0.711.

Step 5: Calculate the Chi-Square Test Statistic

To do this, we first need to calculate the expected frequencies for each cell, assuming the null hypothesis is true (i.e., no relationship exists). The formula for expected frequency is:

E=(RowTotal×ColumnTotal)/GrandTotal

Expected Frequencies (E)

	Training Method	Below Expectations	Meets Expectations	Exceeds Expectations
	A	B	C	D
1	In-person	22.5	42.75	24.75
2	Online	17.5	33.25	19.25
3	Hybrid	10	19	11

Next, we calculate the chi-square test statistic (χ2) using the formula:

$χ^{2} = \sum \frac{( O - E ) ^{2}}{E}$

Calculating this for all cells and summing them up gives us the final test statistic. In this example, let's say the calculated chi-square value is approximately 21.6.

Step 6: Make a Decision

Compare the calculated chi-square value to the critical value.
Calculated χ2 = 21.6
Critical Value from CHISQ.INV = 0.711

Since the calculated value (21.6) is greater than the critical value (0.711), the result falls within the rejection region.

Conclusion:

We reject the null hypothesis. There is a statistically significant relationship between the training method and employee performance. The data suggests that the different training methods lead to different performance outcomes. The company can now investigate which training method is associated with higher performance ratings.

Result for CHISQ.INV(0.05, 4):

0.711

Result for χ2:

21.6