CHISQ.DIST Function in Google Sheets: Complete Guide

Key Insights

CHISQ.DIST calculates left-tailed chi-square distribution probabilities, essential for goodness-of-fit tests and independence analysis in spreadsheet-based statistical work.
The cumulative parameter fundamentally changes the output: TRUE returns the probability that a chi-square value falls below x, while FALSE returns the probability density at exactly x.
Most practical applications require the cumulative form (TRUE), but understanding both modes helps you interpret results correctly and avoid common statistical mistakes.

Introduction to CHISQ.DIST

The chi-square distribution is one of the most frequently used probability distributions in statistical hypothesis testing. It describes the distribution of a sum of squared standard normal random variables, making it fundamental for tests involving categorical data, variance analysis, and model fitting.

Google Sheets provides CHISQ.DIST for calculating left-tailed chi-square distribution values. This function answers the question: “What’s the probability that a chi-square random variable is less than or equal to a given value?” This left-tailed approach differs from CHISQ.DIST.RT, which calculates right-tailed probabilities (greater than or equal to).

The distinction matters because chi-square tests typically use right-tailed critical regions. However, CHISQ.DIST remains valuable for calculating cumulative probabilities, building custom statistical workflows, and understanding the full distribution shape.

Syntax and Parameters

The CHISQ.DIST function follows this syntax:

=CHISQ.DIST(x, degrees_freedom, cumulative)

Parameter breakdown:

x (required): The value at which to evaluate the distribution. Must be greater than or equal to 0. This represents your chi-square test statistic or the point where you want to calculate probability.
degrees_freedom (required): The number of degrees of freedom, which must be a positive integer between 1 and 10^10. For goodness-of-fit tests, this equals (number of categories - 1). For independence tests, it’s (rows - 1) × (columns - 1).
cumulative (required): A logical value determining the function type. TRUE returns the cumulative distribution function (CDF). FALSE returns the probability density function (PDF).

Here’s a basic example showing both output types:

// Cell A1: Chi-square value
=3.84

// Cell B1: Cumulative probability (CDF)
=CHISQ.DIST(A1, 1, TRUE)
// Returns: 0.9500 (approximately)

// Cell C1: Probability density (PDF)
=CHISQ.DIST(A1, 1, FALSE)
// Returns: 0.0297 (approximately)

The cumulative result tells you that approximately 95% of chi-square values with 1 degree of freedom fall below 3.84. The density result gives the height of the probability curve at that exact point.

Understanding the Cumulative Parameter

The cumulative parameter is where most users get confused. Let’s clarify with concrete examples.

Cumulative = TRUE (CDF)

Returns P(X ≤ x), the probability that a chi-square random variable is less than or equal to x. Use this when you need:

P-values (though you’ll often need 1 minus this value)
Percentile calculations
Confidence interval bounds

Cumulative = FALSE (PDF)

Returns the probability density at exactly x. The PDF doesn’t give you a direct probability—it gives the relative likelihood at a specific point. Use this when you need:

To plot the distribution curve
To understand the shape of the distribution
Advanced statistical calculations involving integration

// Comparing TRUE vs FALSE across different x values
// Degrees of freedom = 5

| x Value | CDF (TRUE)              | PDF (FALSE)              |
|---------|-------------------------|--------------------------|
| 1       | =CHISQ.DIST(1,5,TRUE)   | =CHISQ.DIST(1,5,FALSE)   |
|         | → 0.0374                | → 0.0806                 |
| 5       | =CHISQ.DIST(5,5,TRUE)   | =CHISQ.DIST(5,5,FALSE)   |
|         | → 0.5841                | → 0.1220                 |
| 10      | =CHISQ.DIST(10,5,TRUE)  | =CHISQ.DIST(10,5,FALSE)  |
|         | → 0.9247                | → 0.0378                 |
| 15      | =CHISQ.DIST(15,5,TRUE)  | =CHISQ.DIST(15,5,FALSE)  |
|         | → 0.9899                | → 0.0063                 |

Notice how the CDF increases monotonically toward 1, while the PDF rises to a peak then falls. For 5 degrees of freedom, the PDF peaks near x = 3 (degrees of freedom minus 2 for df > 2).

Practical Use Cases

Goodness-of-Fit Testing

Suppose you’re analyzing whether website traffic follows an expected distribution across weekdays. You expected equal traffic, but observed different patterns.

// Setup: Expected vs Observed visitors per day
// A1:A5 = Days (Mon-Fri)
// B1:B5 = Observed: 180, 220, 195, 210, 195 (Total: 1000)
// C1:C5 = Expected: 200, 200, 200, 200, 200 (Total: 1000)

// Calculate chi-square statistic manually
// D1 formula: =((B1-C1)^2)/C1
// Copy D1 to D2:D5

// D1: =((180-200)^2)/200 → 2.00
// D2: =((220-200)^2)/200 → 2.00
// D3: =((195-200)^2)/200 → 0.125
// D4: =((210-200)^2)/200 → 0.50
// D5: =((195-200)^2)/200 → 0.125

// Sum chi-square components
// D6: =SUM(D1:D5) → 4.75

// Calculate left-tailed probability
// D7: =CHISQ.DIST(D6, 4, TRUE) → 0.6907

// Calculate p-value (right-tailed)
// D8: =1-CHISQ.DIST(D6, 4, TRUE) → 0.3093
// Or use: =CHISQ.DIST.RT(D6, 4) → 0.3093

With a p-value of 0.31, you’d fail to reject the null hypothesis at α = 0.05. The traffic distribution doesn’t significantly differ from the expected equal distribution.

Independence Testing for Survey Data

Testing whether product preference is independent of customer age group:

// Contingency table in A1:C3
//              Product A    Product B    Product C
// Age 18-35      45           30           25
// Age 36-55      35           45           20
// Age 56+        20           25           55

// Chi-square statistic (calculated separately or via CHISQ.TEST)
// Let's say χ² = 28.5

// Degrees of freedom = (3-1) × (3-1) = 4
// E1: =CHISQ.DIST(28.5, 4, TRUE) → 0.99999

// P-value
// E2: =1-E1 → 0.00001 (approximately)

This extremely low p-value indicates strong evidence that product preference depends on age group.

Working with CHISQ.DIST in Data Analysis

For robust hypothesis testing, combine CHISQ.DIST with related functions:

// Complete hypothesis test workflow
// Given: Chi-square statistic = 12.4, df = 5, α = 0.05

// Step 1: State your chi-square statistic
// A1: 12.4

// Step 2: Define degrees of freedom
// A2: 5

// Step 3: Set significance level
// A3: 0.05

// Step 4: Calculate critical value using CHISQ.INV.RT
// A4: =CHISQ.INV.RT(A3, A2) → 11.07

// Step 5: Calculate p-value
// A5: =1-CHISQ.DIST(A1, A2, TRUE) → 0.0296
// Or: =CHISQ.DIST.RT(A1, A2) → 0.0296

// Step 6: Make decision
// A6: =IF(A1>A4, "Reject H0", "Fail to Reject H0")
// Returns: "Reject H0"

// Alternative decision using p-value
// A7: =IF(A5<A3, "Reject H0", "Fail to Reject H0")
// Returns: "Reject H0"

You can also use CHISQ.TEST to calculate p-values directly from observed and expected ranges:

// Using CHISQ.TEST for direct p-value calculation
// Observed data in B2:D4
// Expected data in B7:D9

=CHISQ.TEST(B2:D4, B7:D9)
// Returns the p-value directly

Common Errors and Troubleshooting

#NUM! Error

Occurs when:

x is negative
degrees_freedom is less than 1 or greater than 10^10
degrees_freedom is not an integer (Google Sheets truncates decimals, but extreme values cause issues)

// These formulas return #NUM!
=CHISQ.DIST(-2, 5, TRUE)    // Negative x
=CHISQ.DIST(5, 0, TRUE)     // Zero degrees of freedom
=CHISQ.DIST(5, -3, TRUE)    // Negative degrees of freedom

#VALUE! Error

Occurs when parameters aren’t numeric:

// These formulas return #VALUE!
=CHISQ.DIST("five", 5, TRUE)    // Text instead of number
=CHISQ.DIST(5, "five", TRUE)    // Text for degrees of freedom

Error-Handling Wrapper

Build robust formulas with IFERROR:

// Basic error handling
=IFERROR(CHISQ.DIST(A1, B1, TRUE), "Invalid input")

// More informative error handling
=IF(A1<0, "X must be ≥ 0",
  IF(B1<1, "DF must be ≥ 1",
    IF(NOT(ISNUMBER(A1)), "X must be numeric",
      IF(NOT(ISNUMBER(B1)), "DF must be numeric",
        CHISQ.DIST(A1, B1, TRUE)))))

Function	Purpose	Returns	When to Use
CHISQ.DIST	Left-tailed CDF or PDF	P(X ≤ x) or density	Cumulative probability calculations
CHISQ.DIST.RT	Right-tailed CDF	P(X ≥ x)	Direct p-value for chi-square tests
CHISQ.INV	Inverse left-tailed	x value for given probability	Finding values at specific percentiles
CHISQ.INV.RT	Inverse right-tailed	x value for given right-tail probability	Finding critical values for hypothesis tests
CHISQ.TEST	Independence/goodness-of-fit test	p-value	Direct testing from observed/expected data

Quick selection guide:

Need a p-value from a chi-square statistic? Use CHISQ.DIST.RT or 1-CHISQ.DIST(..., TRUE)
Need the critical value for a significance level? Use CHISQ.INV.RT
Need to test data directly without calculating the statistic? Use CHISQ.TEST
Need to understand the distribution shape or calculate left-tailed probabilities? Use CHISQ.DIST

Master CHISQ.DIST and its related functions, and you’ll handle categorical data analysis entirely within Google Sheets—no external statistical software required.