CHISQ.DIST

Returns the chi-squared distribution.

Syntax:

CHISQ.DIST(x, deg_freedom, cumulative)

x is required, and is the value that you want to use to evaluate the distribution.

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

cumulative is required, and is a logical value:

TRUE: Returns the cumulative distribution function (CDF), which gives the probability that the chi-squared random variable is less than or equal to x.

FALSE: Returns the probability density function (PDF), which represents the likelihood of the random variable taking on a specific value.

Example:

If x contains 7.815, deg_freedom contains 3 and cumulative contains TRUE:

CHISQ.DIST(7.815, 3, TRUE)

returns 0.950006097

This example finds the probability of getting a chi-square value less than or equal to 7.815, using 3 degrees of freedom.

x:

Deg_freedom:

Cumulative:

Result:

0.950006097

Application:

A Bag of Candy

Let's imagine you work for a candy company that claims each bag of assorted candies has an equal distribution of five colors: Red, Blue, Green, Yellow, and Orange. You are conducting a quality control check to see if this claim is accurate.

You randomly select a bag of candy and count the number of each color.

Step 1: Set up the Observed and Expected Frequencies

Your claim (the null hypothesis, H0) is that the colors are distributed equally. The alternative hypothesis (HA) is that they are not.

Since there are 200 total candies and 5 colors, the expected frequency for each color is 200/5=40.

Here is a table of your observed counts and the expected counts:

	Color	Observed Frequencies (O)	Expected Frequencies (E)
	A	B	C
1	Red	48	40
2	Blue	35	40
3	Green	30	40
4	Yellow	45	40
5	Orange	42	40
6	Total	200	200

Step 2: Calculate the Chi-Square Test Statistic (χ2)

The formula for the chi-square test statistic is:

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

You would perform this calculation for each color and sum the results.

	Color	(O - E)	(O - E)2	(O - E)2 / E
	A	B	C	D
1	Red	8	64	1.6
2	Blue	-5	25	0.625
3	Green	-10	100	2.5
4	Yellow	5	25	0.625
5	Orange	2	4	0.1
6	Total			5.45

Your calculated chi-square test statistic is χ2=5.45.

Step 3: Use the CHISQ.DIST function to find the p-value

The CHISQ.DIST function calculates the probability of getting a chi-square value less than or equal to your calculated statistic. In hypothesis testing, we are typically interested in the probability of getting a result as extreme or more extreme than the one we observed, which is the right-tailed probability. The CHISQ.DIST function with cumulative = TRUE gives you the left-tailed probability, so you must subtract the result from 1 to get the p-value.

The function has the following syntax: CHISQ.DIST(x, deg_freedom, cumulative)

x: The chi-square test statistic (5.45 in our example).
deg_freedom: The degrees of freedom, which is the number of categories minus 1. In our case, 5−1=4.
cumulative: TRUE (or 1) to get the cumulative distribution function, which is what we need for a p-value calculation.

The Formula:

You would enter the following formula into a cell in your spreadsheet:

1 - CHISQ.DIST(5.45, 4, TRUE)

The Result:

The function returns a p-value of approximately 0.244.

Step 4: Interpret the Result

You compare the p-value to a pre-determined significance level (alpha, α). A common alpha level is 0.05.

P-value (0.244) > α (0.05): Because our p-value is greater than the significance level, we do not reject the null hypothesis.

This means that the differences we observed between our candy counts and the expected counts are not statistically significant. It is likely that the variations in color counts were just due to random chance, and the manufacturer's claim of an equal distribution of colors is supported by the data.

Result for 1 - CHISQ.DIST(5.45, 4, TRUE):

0.244