The following article introduces you to the CHISQ.TEST function - one of the functions in the statistical group is very popular in Excel.
Description: The function returns the independence test. The function returns the value from the distribution (({chi ^ 2})) when squared for statistics and the appropriate degrees of freedom. Support function from Excel 2010 version.
Syntax: CHISQ.TEST (actual_range, expected_range)
Inside:
- actual_range: The range of data that contains observations to test against the expected value.
- expected_range: The data range contains the ratio of the sum of the row and column totals to the total.
Attention:
- If actual_range, expected_range has a different number of data points -> the function returns the # N / A error value .
- Test ({chi ^ 2}) calculate statistics ({chi ^ 2}) using the formula:
[{chi ^ 2} = sumlimits_ {i = 1} ^ r {sumlimits_ {j = 1} ^ c {frac {{{{left ({{A _ {{rm {ij}}}} - {E _ {{rm {ij}}}}} right)} ^ 2}}} {{{E _ {{rm {ij}}}}}}}}]
Inside:
+ ({{A _ {{rm {ij}}}}}): Is the actual frequency in the i th row, j th column
+ ({{E _ {{rm {ij}}}}}): Is the expected frequency in the i th row, j th column
+ r: Number of rows
+ c: Number of columns
- The lower value of ({chi ^ 2}) is independent. ({chi ^ 2}) is always positive or equal to 0 and only 0 if and only if ({{A _ {{rm {ij}}}}}) = ({{E _ {{rm {ij}}}}}} ) with all i, j.
- Use the CHISQ.TEST function best when the ({{E _ {{rm {ij}}}}}) is not too small
For example:
Statistics ({chi ^ 2}) for the independence test according to the data in the table below:
- In the cell to calculate enter the formula: = CHISQ.TEST (C7: D9, E7: F9)
- Press Enter -> Statistics ({chi ^ 2}) for the independence test are:
Above are instructions and some specific examples when using the CHISQ.TEST function in Excel.
Good luck!