CONFIDENCE.T

What it does

CONFIDENCE.T(alpha, standard_dev, size)

CONFIDENCE.T(alpha, standard_dev, size) returns the half-width of a two-sided confidence interval for a population mean when the standard deviation was estimated from the sample rather than known in advance.

Writing t_{q,ν} for the q-quantile of Student's t-distribution with ν degrees of freedom,

CONFIDENCE.T(α, s, n)  =  t_{1 − α/2, n − 1} · s / √n

and the interval is x̄ ± CONFIDENCE.T(α, s, n).

The degrees-of-freedom count is n − 1, not n: one degree of freedom was spent estimating the mean before the spread was measured. That single subtraction is the whole difference between this function and CONFIDENCE.NORM in structure, and it is the reason the smallest usable sample here is n = 2 rather than n = 1.

The t-distribution itself. Its density on ν > 0 degrees of freedom is

f(t; ν)  =  Γ((ν+1)/2) / ( √(νπ) · Γ(ν/2) ) · (1 + t²/ν)^(−(ν+1)/2)

symmetric about zero, with tails heavier than the normal's — f(t; ν) ~ C·|t|^(−(ν+1)), so only moments below order ν exist, and the variance ν/(ν−2) exists only for ν > 2. As ν → ∞ the density converges to the standard normal's, and correspondingly t_{q,ν} → z_q from above. Because t_{q,ν} > z_q for every finite ν and every q > 1/2, CONFIDENCE.T is always at least as wide as CONFIDENCE.NORM on the same three arguments, dramatically so for small n.

The CDF is an incomplete beta function. With I_x(a,b) the regularized incomplete beta,

P(T ≤ t; ν)  =  1 − ½ · I_{ν/(ν+t²)}(ν/2, ½)     for t ≥ 0

Domain and range: α ∈ (0,1), s > 0, n ≥ 2, and the value is positive throughout. It diverges as α → 0⁺, tends to 0 as α → 1⁻, is linear in s, and falls roughly like n^(−1/2) with a second, faster contribution from the shrinking t quantile as the degrees of freedom grow.

Arguments

The misunderstood positions are the same two as on the normal sibling, plus one specific to this function:

• alpha is the significance level, not the confidence level. 0.05 means 95%.
• size is documented as truncated when non-integer.
• The degrees of freedom are derived, not supplied. A reader who has already computed n − 1 elsewhere and passes it here gets an interval one degree of freedom too wide.

All three are numeric slots under ordinary to-number coercion (chapter 02).

Try it

Quirks

Result and edge cases

Returns Number — a positive half-width.

Documented boundary behaviour, from Microsoft's page:

• Any nonnumeric argument → #VALUE!.
• alpha ≤ 0 or alpha ≥ 1 → #NUM!.
• standard_dev ≤ 0 → #NUM!.
• size non-integer → truncated.
• size = 1 → #DIV/0!. This is the one documented error condition with no counterpart on CONFIDENCE.NORM, and it is mathematically the right shape: at n = 1 there are zero degrees of freedom and the t quantile does not exist.

What the documentation does not say: what size < 1 does. The normal sibling documents size < 1 → #NUM!; this page documents only the size = 1 case. So CONFIDENCE.T(0.05, 1, 0) is undocumented behaviour.

Errors

As documented on Microsoft's CONFIDENCE.T page:

A documentation defect on the Microsoft page

Microsoft's CONFIDENCE.T page describes standard_dev as "The population standard deviation for the data range and is assumed to be known" — word for word the sentence on the CONFIDENCE.NORM page.

For CONFIDENCE.T that sentence is self-contradicting. If the population standard deviation were known, the correct interval would use the normal quantile and there would be no reason for this function to exist; the t-distribution enters precisely because s is a sample estimate with its own sampling variability. The wording appears to have been copied from the normal page when the 2010 dotted pair was created.

The Handbook records this as a documentation defect, not as a behaviour claim: nothing about what the function computes is in doubt, only what the page says the argument is. Readers deciding between the two functions should use the operative rule — known σ takes CONFIDENCE.NORM, sample s takes CONFIDENCE.T — and not the argument prose.

How to implement

Numerical notes

Three sources of error stack in this function, and only the first is specific to it.

3. The composition. t · s / √n — one multiplication, one square root, one division, and a choice of association that changes the last bit.

Standard references for the routines involved: Abramowitz & Stegun chapter 26.7 (Student's t, its incomplete-beta form, and the percentage-point expansions); Didonato & Morris, ACM TOMS 708 (BRATIO); Cephes incbet/incbi; Boost's students_t quantile; Numerical Recipes' betai and betacf (readable, and explicitly less accurate than the TOMS routine).

Rust code

15 confidence_t "CONFIDENCE.T" (3, 3) [V "alpha", V "standard_dev", V "size"] |s, a, sv| {
    let [alpha, sd_, n] = nums::<3>(s, a)?;
    out(s, ct::confidence_t_kernel(alpha, sd_, n))
}

pub fn confidence_t_kernel(
    alpha: f64,
    standard_dev: f64,
    size: f64,
) -> Result<f64, WorksheetErrorCode> {
    if !alpha.is_finite() || !standard_dev.is_finite() || !size.is_finite() {
        return Err(WorksheetErrorCode::Value);
    }
    let n = size.trunc();
    if !(0.0 < alpha && alpha < 1.0) || standard_dev <= 0.0 || n < 2.0 {
        return Err(WorksheetErrorCode::Num);
    }
    Ok(t_inv_2t_kernel(alpha, n - 1.0)? * standard_dev / n.sqrt())
}

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

Relationships

• CONFIDENCE.NORM — the known-σ sibling. Same shape, z_{1−α/2} in place of t_{1−α/2,n−1}. Not a legacy/modern pair: both are current, and both were introduced in Excel 2010 alongside the legacy CONFIDENCE.
• CONFIDENCE — the pre-2010 spelling, which corresponds to the normal function. There is no legacy spelling of CONFIDENCE.T; it is new function surface, not a rename. This matters when reading migration tables that treat every dotted name as a rename.
• STDEV.S — the usual supplier of standard_dev. Feeding STDEV.P here instead is a common and silent error: it estimates with the wrong divisor and then pairs it with a t quantile that assumes the other one.

Named variants

No named variants are recorded yet.