The chisquare test uses the chisquare distribution, to test whether there is a significant difference between observed frequencies and expected frequencies for a data set.
The chisquare distribution is given by the formula :
where,
A_{ij}  =  actual frequency in the i'th row & j'th column 
E_{ij}  =  expected frequency in the i'th row & j'th column 
r  =  number of rows 
c  =  number of columns 
The chisquare test gives an indication of whether the value of the chisquare distribution, for independent sets of data, is likely to have occurred by chance alone.
The Excel CHISQ.TEST function performs the chisquare test on two supplied data sets (of observed and expected frequencies), and returns the probability that the differences between the sets are simply due to sampling error.
The function is new in Excel 2010, and so is not available in earlier versions of Excel. However, the Chisq.Test function is simply an updated version of the Chitest Function, that is available in earlier versions of Excel.
The syntax of the function is :
Where the function arguments are:
actual_range    An array of observed frequencies 
expected_range    An array of expected frequencies 
The supplied actual_range and the expected_range arrays must have equal dimensions.
It is important to note that the chisquare test is not reliable with very small expected values. As a guideline, if any of the expected values are less than 5, or if the total of the expected values is less than 50, the result of the chisquare test should not be relied upon.
The spreadsheet below contains the observed and expected frequencies of the responses of two groups, (men and women), to a simple question.
The Excel Chisq.Test function can be used to calculate the chisquare test for independence, for the above data sets. The formula for this is:
=CHISQ.TEST( B3:C5, F3:G5 )
which gives the result 0.000699103.
Generally, a probability of 0.05 or less is considered to be significant. Therefore, the above returned value, 0.000699103, indicates a significant difference between the observed and the expected frequencies, which is unlikely to be due to sampling error.
Further examples of the Excel Chisq.Test function can be found on the Microsoft Office website.
If you get an error from the Excel Chisq.Test function this is likely to be one of the following :
#N/A   
Occurs if either:


#DIV/0!    Occurs if any of the expected_values are zero  
#NUM!    Occurs if any of the expected_values are negative 