CHISQ.TEST Function

Excel, developed by Microsoft in 1985, is a widely used spreadsheet software application. It is used by businesses and working professionals to store and analyze data efficiently.

With Excel, data analysts can utilize charting and graphing capabilities to interpret data visually and intuitively. It also provides many functions and features to perform complex mathematical, statistical, and arithmetic operations on data, yielding valuable insights and conclusions.

By automating mathematical calculations, Excel helps save time as we no longer have to perform them manually. This efficiency makes it a highly efficient and time-saving software for companies to store, analyze, and structure their data.

We have seen different types of functions and their use cases in Excel: financial functions, statistical functions, auditing functions, and arithmetic functions. These functions help in different scenarios based on the user’s requirements.

In this article, we will focus on the CHISQ.TEST function, which falls under the category of statistical functions. Statistical functions are used in Excel when dealing with probability distributions and their statistical implications.

what is the CHISQ.TEST Function in Excel?

It is one of the statistical functions in Excel that calculates the chi-square distribution of the two datasets, especially the observed and expected frequencies.

This function helps to understand whether the differences between the datasets are due to some sampling error or other factors.

Statistically, the chi-square distribution is the squared sum of independent standard normal random variables.

If k is the number of degrees of freedom of the distribution, it would have k square terms. It helps in inferential statistics, hypothesis testing, and constructing confidence intervals for statistical analysis.

The following formula gives the chi-square distribution:

Here:

• A_ij refers to the actual/observed frequency in the i’th row and j’th column
• E_ij refers to the expected frequency in the i’th row and j’th column
• r refers to the number of rows
• c refers to the total number of columns

The chi-square test indicates whether the value of the chi-square distribution for independent data sets is likely to happen by chance only. The formula for the CHISQ.TEST function is:

=CHISQ.TEST(actual_range, expected_range)

The terms in parentheses are called the arguments of the function. These are the values the function requires to perform the computations and produce the desired result. The arguments required by the CHISQ.TEST function are:

1. actual_range

This argument refers to the range of data containing observations to be tested against the expected values. In other words, it is an array of the observed frequencies.

2. expected_range

This argument refers to the range of data containing the ratio of the product of the sum of rows and the sum of columns to the total of the entire dataset. In other words, it is an array of the expected frequencies.

The actual_range and the expected_range must have the same dimensions.

Examples of the CHISQ.TEST Function in Excel

Having looked at the theoretical definition of the CHISQ.TEST function, let us now see an example to practically understand the applications and usage of this function in the industry.

Suppose we have two students, John and Sam, who perform a statistical experiment on a chi-square distribution with three independent datasets. The observed/actual frequencies they got and the expected frequencies are shown in the Excel worksheet below:

To calculate the chi-square test for verifying the independence of the above datasets using it in Excel, we use the following formula:

=CHISQ.TEST(C6:D8, F6:G8)

On using the above formula in cell C10 of our Excel worksheet, we get the following result:

A low value of the chi-squared test indicates a greater level of independence. A number of 0.05 or less typically denotes a significant difference between actual and predicted frequencies that is not the result of sampling error.

In the above example, the value obtained is sufficiently less than 0.05. So the observed and expected frequencies differ significantly, and this difference is unlikely due to any sampling error. There must be some other cause responsible for the difference.

Types of Errors in the CHISQ.TEST Function

The commonly encountered errors while using it in Excel is as follows:

1. #NUM! Error

When any value in the expected range is negative, the function returns #NUM! Error. The CHISQ.TEST function cannot handle negative values, so ensure all values in the expected range are positive.

2. #N/A Error

The function shows this error in two cases:

• If the data arrays for the observed and expected ranges are of different dimensions.
• If the data arrays for the observed and expected ranges contain only 1 value, implying the length and width are equal to 1.

3. #DIV/0! Error

When any of the values in the data array for the expected range is 0, the function returns #DIV/0! Error.

In this way, we can use it in Excel to calculate the value of the chi-squared test and hence analyze the independence of the observed and expected frequencies.

If a small number is obtained in this test(less than 0.05), it indicates significant discrepancies between the observed and expected frequencies, which is not likely due to sampling error.

Researched and Authored by Devang Shekhar | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• CHISQ.INV Function
• CHISQ.INV.RT Function
• DURATION Function
• EDATE Function
• EFFECT Function