Create a chi squared distribution in Excel

In my last blog post I discussed how to create a gamma distribution in Excel, and that post can be read here:- https://tracyrenee61.medium.com/create-a-gamma-distribution-in-excel-3e05a2408544

In this post I intend to discuss how to create a chi squared distribution in Excel.

The chi squared distribution is one of the most important continuous probability distributions with many uses in statistical theory and inference. Chi squared is not only a distribution,but a statistical test as well. In 1900, English mathematician Karl Pearson introduced the distribution in a seminal paper. Pearson showed that the chi squared distribution arose from such a multivariate normal approximation to the multinomial distribution, taking careful account of the statistical statistical dependence (negative correlations) between the numbers of observations in different categories.

The chi squared distribution is a continuous probability distribution that is used in many hypothesis tests including the chi squared goodness of fit test and the chi squared test of independence. The main purpose of the chi squared distribution is to perform hypothesis testing and not for real world situations.

Chi squared distributions are useful for hypothesis testing because of their close relationship, which has a mean of 0 and a variance of 1, central to many important statistical tests and theories.

The formula for chi-squared distribution is:-

In order to create the chi squared distribution, it is first important to determine how many degrees of freedom the distribution will be composed of. Degrees of freedom are the numbers of logically independent values that may vary in a data sample. In this instance, I chose 10 degrees of freedom in cell F2.

The next step is to enter the input values, and I selected values of 0 to 20 in cells A3 to A23.

Once the degrees of freedom and the input values have been entered into the worksheet, I entered the following formula in cell B3 and copied it down to cell B23 to create a chi squared probability distribution function (PDF):-

=CHISQ.DIST(A3, $F£2, FALSE)

The resulting PDF chart that is created can be seen below:-

I then entered the following formula in cell D3 and copied it down to cell D23 to create a chi squared cumulative distribution function (CDF):-

=CHISQ.DIST(A3, $F£2, TRUE)

The resulting CDF chart that is created can be seen below:-

The analyst can experiment with the degrees of freedom and input values to change the shape of the PDF and CDF curves created.

I have created a video to accompany this blog post, and it can be viewed here:- https://youtu.be/FqRJ4Ft5eBY

If you would like to know about other statistical distributions then please refer to my blog post that reviews 25 popular distributions in Python:- https://medium.com/@tracyrenee61/a-review-of-the-25-most-popular-distributions-in-statistics-using-python-eabae1ddfb45