CHISQ.INV

What it does

CHISQ.INV(probability, deg_freedom)

CHISQ.INV(probability, deg_freedom) finds the chi-square value whose left cumulative probability is the requested value.

Try it

=CHISQ.INV(0.5,2) is about 1.38629436111989. At two degrees of freedom, a half right tail solves exp(-x/2)=0.5; the same point also has a half left CDF.

Quirks

Degrees of freedom are truncated. Microsoft documents #NUM! below one or above 10^10, and for probabilities outside zero to one; nonnumeric arguments give #VALUE!.

The current left inverse returns zero at probability zero and #NUM! at one. Probability one has no finite mathematical quantile, despite being inside the range described by the page’s out-of-range test. The companion right-tail inverse uses a different endpoint policy and is not obtained by relabelling the same probability.

How to implement

1. Truncate and validate the degree count k, including the current upper limit 10^10. Validate finite p and handle its endpoints.
2. Call the current GAMMA.INV kernel with probability p, shape k/2 and scale two. This shared composition is supported by the source’s retained 63 interior pairs; it is not a claim that any gamma library can be substituted unchanged.
3. That inverse uses its own generic lower incomplete-gamma forward callback. It expands an upper x bound and bisects the ordered binary64 encodings until the endpoints are adjacent, then returns the upper one. Preserve its callback and return rule rather than stopping when an arbitrary real-valued residual looks small.

At k=2, the mathematical CDF is 1-exp(-x/2). Probability one half therefore gives x=-2*ln(0.5), about 1.38629436111989. This identity explains the example; the implementation still goes through the shared inverse.

The public GAMMA.DIST wrapper has special-case branches that the inverse callback does not automatically inherit. Complete forward last bits and all inverse edge cases remain open; the 63-pair composition does not identify private Excel source or settle the whole domain.

Rust code

23 chisq_inv "CHISQ.INV" (2, 2) [V "probability", V "deg_freedom"] |s, a, sv| {
    let [p, df] = nums::<2>(s, a)?;
    out(s, cft::chisq_inv_kernel(p, df))
}

pub fn chisq_inv_kernel(probability: f64, deg_freedom: f64) -> Result<f64, WorksheetErrorCode> {
    let k = truncate_positive_integer(deg_freedom)?;
    // Inverse-problem identity, live Excel 16.0 b20228: CHISQ.INV(p, df)
    // == GAMMA.INV(p, df/2, 2) on 63/63 interior pairs. Endpoints also
    // match: CHISQ.INV(0,2)=0 and CHISQ.INV(1,2)=#NUM!. CHIINV /
    // CHISQ.INV.RT is the right-tail inverse, not GAMMA.INV(1-p,...) (34/63).
    if !probability.is_finite() {
        return Err(WorksheetErrorCode::Num);
    }
    crate::ported::beta_gamma_stats_family::gamma_inv_kernel(probability, k / 2.0, 2.0).map_err(
        |e| match e {
            code => code,
            _ => WorksheetErrorCode::Value,
        },
    )
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

CHISQ.DIST supplies the forward probability; CHISQ.TEST returns a test p-value directly.

Named variants

No named variants are recorded yet.