What is CHITEST in Google Sheets?
The CHITEST function in Google Sheets calculates the probability (p-value) that differences between observed and expected frequencies occurred by chance. It is commonly applied in hypothesis testing to test whether a distribution is uniform or whether two categorical variables are independent. The closer the p-value is to 0, the less likely it is that the observed differences happened randomly.
For instance, you can use CHITEST to test the fairness of a die or flipping of a coin. Suppose we must check if a coin is fair. We flip it 50 times and get 28 heads and 22 tails. The expected outcome is 25 heads and 25 tails. We enter it in a sheet as shown below.
Using =CHITEST(A2:A3, B2:B3), you get a p-value of 0.39, meaning there’s no significant evidence that the coin is biased. If the function returns a small p-value (below 0.05), it suggests the observed data significantly differs from what was expected.
Key Takeaways
CHITEST in Google Sheets returns the p-value of a chi-square test, used to determine whether observed and expected data differ significantly.
The syntax of the function is as follows: =CHITEST(observed_range, expected_range)
- observed_range – The actual data values collected.
- expected_range – The theoretically expected frequencies or predictions.
A small p-value (<0.05) indicates a statistically significant difference, while a larger value suggests that any variation is likely due to chance.
CHITEST requires both ranges to have identical dimensions and contain only positive numeric values for accurate results.
Syntax
The Google Sheets CHITEST function helps assess whether observed and expected data differ significantly. The CHITEST Google Sheets formula is:
=CHITEST(actual_range, expected_range)
Arguments:
- actual_range: The range of observed frequencies (the data collected from real events).
- expected_range: The range of expected frequencies (theoretical or predicted counts).
The function returns a p-value between 0 and 1.
Meaning
- p < 0.05: Significant difference — the variation is unlikely due to chance.
- p ≥ 0.05: No significant difference — the variation can be attributed to random chance.
How To Use CHITEST Function in Google Sheets?
The CHITEST function is ideal for testing whether observed frequencies differ significantly from what’s expected under a given hypothesis. Let’s understand this with a practical example. There are two ways to enter the function. One is manual and the other is through the Sheets menu.
Entering CHITEST Manually
Suppose we must test if a six-sided die is fair. We roll it 60 times and record how many times each side appears. Since a fair die should show each number about 10 times, we compare the observed data with this expected distribution.
Step 1: Enter the observed and expected data in a Google Sheet as shown below.
Step 2: Click on a empty cell and enter the formula:
=CHITEST(A2:A7, B2:B7)
Step 3: Press Enter. Wel get a result of approximately 0.051 (or 5.1%). This means there’s a 5.1% chance that the differences between observed and expected results occurred purely by chance. This suggests the die might not be perfectly fair.
Using CHITEST From the Google Sheets Menu
- Select the cell where you want the p-value to appear.
- Click Insert → Function → Statistical.
- Choose CHITEST from the list of functions.
- Enter the ranges for the observed and expected data.
- Press Enter to calculate the p-value.
Examples
Let us look at some practical examples of how CHITEST can be used in Google Sheets
Example #1 – Compare the sales data from before and after the campaign
There is an organization that must check if a new marketing campaign has significantly changed sales distribution across three product categories. The team records observed sales (after the campaign) and compared them with expected sales that were estimated before the campaign. We can use CHITEST in Google sheets to check the impact of the campaign.
Step 1: Enter the data in Google Sheets as shown below.
Step 2: Click on a blank cell and type the following formula.
=CHITEST(A2:A4, B2:B4)
Step 3: Press Enter. The result will be approximately 0.26, indicating a 26% probability that the difference in sales distribution occurred by chance.
There is no statistically significant change in sales distribution after the campaign. Hence, the campaign didn’t dramatically alter customer buying patterns.
Example #2 – Compare your progress from before and after the workout routine
A fitness coach wants to evaluate whether a new workout plan has led to an improvement in their client’s activity levels. The coach tracks five types of exercises and compares the observed and expected session counts to check if the difference is statistically significant. He can do this using CHITEST in Google Sheets.
Step 1: Enter the data in Google Sheets as shown below.
Step 2: Click on cell B7 and type the formula:
=CHITEST(A2:A6, B2:B6)
Step 3: Press Enter. The result shows that there’s a 75% probability that the variation occurred by chance.
Step 4: To understand it visually, we can plot a chart.
- Select the range A1:B6.
- Go to Insert → Chart.
- Choose Column Chart to compare the before-and-after workout results.
If the p-value is greater than 0.05, it suggests the difference in your workout performance isn’t statistically significant. However, a lower p-value would indicate that the new routine has likely made a real difference.
The chart shows that that while there’s visible progress in activity, the overall improvement is not statistically significant yet.
Example #3 – Compare productivity before and after implementing the management technique
A company has introduced a new management technique to improve productivity across departments. The management compares the number of projects completed before and after implementation using the CHITEST function in Google Sheets.
Step 1: Enter the data in your Google Sheet as shown below.
Step 2: Click on cell B7 and type the formula:
=CHITEST(A2:A6, B2:B6)
Step 3: Press Enter. The result will be approximately 0.87, indicating a 87% probability that the observed differences happened by chance rather than due to the management change. Since it is greater than 0.05, the differences in productivity are not statistically significant. While applying conditional formatting may show some gains, these changes might be due to normal fluctuations rather than the management method itself.
Step 4: You can visually analyze which departments improved by applying conditional formatting.
Select the range A2:A6 that contains the observed data.
- Go to Format → Conditional formatting.
- Under Format rules, choose Color scale.
- Set the following gradient:
- Minpoint: Number → set to the lowest observed value (e.g., 35), color → light red.
- Midpoint: Percentile → 50%, color → yellow.
- Maxpoint: Number → set to the highest observed value (e.g., 50), color → green.
Click Done.
This creates a red-to-green scale where red shows lower productivity and green highlights higher performance.
Important Things to Note
- Both the arguments, observed_range and expected_range, must contain the same number of rows and columns. Any mismatch will cause an error or produce incorrect results.
- A small p-value that is <0.05 usually indicates a significant difference, but the significance threshold may vary depending on the specific case chosen. Always interpret CHITEST results in context of the data provided and the situation.
- If any cell in either range is non-numeric, it and the corresponding cell in the other range do not count toward the calculation.
Frequently Asked Questions (FAQs)
The CHITEST function is widely used in statistical analysis and research to test relationships between categorical data. Some common use cases include:
1. In market research to compare customer preferences or survey results across different groups.
2. Use in quality control to verify if product defects occur randomly or due to a specific factor.
3. In education and psychology to test if observed outcomes differ from expected probabilities.
CHITEST calculates the p-value associated with the chi-square statistic for two data ranges, the observed and expected frequencies. It measures how likely it is that any difference between them occurred by chance. A smaller p-value (< 0.05) suggests a statistically significant difference, while a larger p-value implies the observed variation may just be random.
Some of the common errors include:
1. #N/A error: Here, the observed and expected ranges have different dimensions (for example, 3 rows vs. 4 rows).
2. #NUM! error: If any expected value is zero or negative, as division by zero is not possible in the calculation.
3. #VALUE! error: This error occurs when non-numeric or invalid data types (like text) are included in the range.
To avoid these, always ensure that both ranges contain valid, positive numeric data of the same size.
Download Template
This article must help understand the CHITEST Function in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What Is CHITEST Function In Google Sheets. We explain how to use the CHITEST Function In Google Sheets with examples and points to remember. You can learn more from the following articles. –
Leave a Reply