CHISQTEST

Returns a measure of $χ^{2}$ 'goodness of fit'.

Syntax:

CHISQTEST(observed, expected)

observed and expected are ranges or arrays of observed and expected values. They must have the same number of rows and columns and there must be at least 2 values in each.

CHISQTEST calculates the $χ^{2}$ statistic and degrees of freedom, then calls CHISQDIST.

Example:

CHISQTEST({8,9,7,8}, {8,8,8,8})

returns approximately 0.969.

Application:

TV Ad Campaign Effectiveness

A company wants to determine if the effectiveness of their new TV ad campaign varies across different age groups. They survey 400 people and categorize them by age group and their response to the ad (liked the ad, disliked the ad).

Here is the contingency table showing the observed frequencies:

	Age Group	Liked Ad	Disliked Ad	Total
	A	B	C	D
1	18-30	85	45	130
2	31-50	70	60	130
3	51+	55	85	140
4	Total	210	190	400

The Goal:

We want to test the null hypothesis (H0): The effectiveness of the TV ad is independent of the age group.

The alternative hypothesis (H1): The effectiveness of the TV ad is dependent on the age group.

Using the CHISQTEST function:

You would use the CHISQTEST function to calculate the p-value. The syntax is:

CHISQTEST(actual_range, expected_range)

First, we need to calculate the expected frequencies under the assumption that the null hypothesis is true (i.e., that the variables are independent). The formula for an expected cell value is:

Expected Value = (Row Total * Column Total) / Grand Total

Let's calculate the expected frequencies for our table:

Expected (18-30, Liked): (130∗210)/400=68.25
Expected (18-30, Disliked): (130∗190)/400=61.75
Expected (31-50, Liked): (130∗210)/400=68.25
Expected (31-50, Disliked): (130∗190)/400=61.75
Expected (51+, Liked): (140∗210)/400=73.5
Expected (51+, Disliked): (140∗190)/400=66.5

Here is the table of expected frequencies:

	Age Group	Liked Ad	Disliked Ad
	A	B	C
1	18-30	68.25	61.75
2	31-50	68.25	61.75
3	51+	73.5	66.5

Now, we can use the CHISQTEST function.

Let's say your observed data is in cells B1:C3 from the first table and from the second table your expected data is in cells B1:C3.

The formula would be:

CHISQTEST(B1:C3 from the first table, B1:C3 from the second table)

The function will return a p-value. In this case, the p-value is approximately 0.000093651.

Conclusion:

The p-value is extremely small, far below the typical significance level of 0.05. Since the p-value is less than the significance level, we reject the null hypothesis.

Therefore, we have strong evidence to conclude that there is a statistically significant association between age group and the effectiveness of the TV ad campaign. The company can conclude that the ad's effectiveness is not the same across all age groups.

Result for CHISQTEST:

0.000093651