T.INV.2T

What it does

T.INV.2T(probability, deg_freedom)

T.INV.2T(probability, deg_freedom) returns the two-tailed critical value of Student's t-distribution with ν degrees of freedom. Microsoft states the definition directly: it returns "that value t, such that P(|X| > t) = probability where X is a random variable that follows the t-distribution".

So it is the exact inverse of T.DIST.2T, and it is related to the left-tail quantile of T.INV by

T.INV.2T(p, ν) = T.INV(1 − p/2, ν) = − T.INV(p/2, ν)

This is the number printed in every statistics textbook's t table. Asking for the 5% two-sided critical value at 10 degrees of freedom is T.INV.2T(0.05, 10).

Because P(|T| > t) is continuous and strictly decreasing from 1 at t = 0 to 0 as t → ∞, the inverse exists and is unique on p ∈ (0, 1], with

T.INV.2T(1, ν) = 0        exactly, for every ν
T.INV.2T(p, ν) → +∞       as p → 0⁺

The range is [0, ∞): this surface never returns a negative number. That is worth stating plainly, because it is the fastest way to tell it apart from T.INV, which returns negative values for p < ½.

Closed forms at the two small integer degrees of freedom, obtained by inverting the elementary two-tail formulas:

Both are exact and elementary — a full parameter slice each on which any implementation can be checked against arithmetic instead of against another approximation.

Deep-tail growth: as p → 0⁺ the critical value grows like p^{−1/ν}, so it explodes as a power law, violently for small ν (ν = 1 gives growth proportional to 1/p) and gently for large ν. An implementation whose starting bracket is derived from the normal quantile — which grows only like sqrt(−2 ln p) — will start catastrophically low in the small-ν tail.

Arguments

Documented admissible range: #NUM! when probability ≤ 0 or probability > 1, so probability = 1 is admitted — and here, unlike on T.INV, that is mathematically fine: the answer is exactly 0. The two inverse surfaces have opposite behaviour at their shared documented boundary, which is a small but real reason to keep them straight.

Microsoft documents that a non-integer deg_freedom is truncated, and that one-tailed values can be recovered by passing 2 * probability.

Numeric slots take ordinary to-number coercion; see Coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number, a non-negative critical value.

• probability = 1 is exactly 0. Free and exact.
• probability near 0 is where the critical value is large and where the problem is badly conditioned: dt/dp = −1/(2 f(t; ν)), and the density in the tail is tiny, so a one-ulp change in p moves t a long way. No algorithm can recover accuracy the input does not contain. This is why a tail critical value is a fundamentally harder request than a central one.
• probability near 1 is the opposite corner: t is near 0 and the answer has plenty of absolute room, but computing it from T.INV(1 − p/2, ν) would form 1 − p/2 for p near 1 and lose relative precision in the small quantity. A direct two-tail search does not have that problem, which is an argument for this surface existing rather than being a wrapper.
• Round trip. T.DIST.2T(T.INV.2T(p, ν), ν) should return p. Microsoft's own remark makes this the defining relationship rather than a nice property: the value is found by seeking it.

Errors

As documented on Microsoft's T.INV.2T page:

Errors in either argument propagate under the universal coercion rule. The Handbook has not verified any of this against Excel.

How to implement

Numerical notes

Microsoft is unusually forthcoming here, and what it says shapes the whole section: the page states that T.INV.2T uses an iterative seek for the x at which the forward two-tail function equals probability, and that the precision of T.INV.2T depends on the precision of T.DIST.2T. That is a documented statement about an algorithm, and it is rare enough in Excel's documentation to be worth taking seriously: it says the inverse is not independently accurate, and that any error in the forward surface is inherited.

Three consequences follow.

Error inheritance is not neutral — it is amplified by the conditioning. If the forward surface has relative error ε at t, the induced error in the recovered t is roughly ε · p / f(t; ν). In the tail, where f is tiny, that factor is large. An inverse built by iterating on a forward function that is good to a few ulp can still be poor in absolute terms far out, and no amount of iteration fixes it.

The stopping rule has to be stated in the right variable. Iterating to a fixed tolerance on t over-solves in the centre and under-solves in the tail; iterating to a fixed tolerance on p does the reverse. A defensible implementation iterates on p with a relative tolerance and polishes with one Newton step whose derivative is the density — which is available in closed form from T.DIST(·, ν, FALSE).

There is a better route than iteration. The two-tail critical value follows analytically from the inverse regularized incomplete beta: with y = I⁻¹_p(ν/2, ½), t = sqrt(ν(1 − y)/y). Cephes' incbi, Boost.Math's ibeta_inv, and the DCDFLIB/TOMS lineage all provide the inverse beta with stated error bounds, so a natural-best implementation should take this route and reserve iteration for the polish step. Hill's classic t-quantile algorithm and the expansion in A&S 26.7.5 give the starting approximation when iteration is used; both are stated in terms of the normal deviate and both need a tail correction for small ν, for exactly the power-law reason given above.

Nothing here is a claim about what Excel actually does beyond what its own page says.

Rust code

45 t_inv_2t "T.INV.2T" (2, 2) [V "probability", V "deg_freedom"] |s, a, sv| {
    let [p, df] = nums::<2>(s, a)?;
    out(s, cft::t_inv_2t_kernel(p, df))
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• T.DIST.2T is the forward function this inverts, and Microsoft's page names it as such.
• T.INV is the left-tail quantile, a different function. The conversion is T.INV.2T(p, ν) = T.INV(1 − p/2, ν). Using T.INV(p, ν) where T.INV.2T(p, ν) was meant gives a number of the wrong sign and the wrong magnitude, and it will not look obviously wrong in a spreadsheet.
• TINV is this surface's legacy spelling — not T.INV's. TINV(p, ν) and T.INV.2T(p, ν) are documented to compute the same quantity. Whether they compute it the same way, and publish the same bits, is not established. The Handbook's alias-pairing record for the legacy/modern collapse covers five forward CDF pairs and states in terms that no inverse pair — GAMMAINV/GAMMA.INV, CHIINV/CHISQ.INV.RT, BETAINV/BETA.INV, FINV/F.INV.RT, and TINV/T.INV.2T — has ever been probed for identity. Treating the legacy and modern inverse spellings as interchangeable is an assumption, and this Handbook names it as one.
• T.DIST.RT: there is no T.INV.RT. The right-tail critical value is spelled T.INV(1 − p, ν) or T.INV.2T(2p, ν); the second form is preferable, because it does not form 1 − p.
• CONFIDENCE.T is the packaged consumer: a t-based confidence half-width is T.INV.2T(alpha, n−1) · s / sqrt(n).
• T.TEST goes the other way — it returns a p-value, not a critical value.

Named variants

No named variants are recorded yet.