GAMMA

What it does

GAMMA(number)

GAMMA(x) returns Euler's gamma function, the analytic continuation of the factorial.

For Re z > 0 it is the Euler integral of the second kind:

Γ(z)  =  ∫₀^∞  t^{z−1} e^{−t}  dt

and everywhere else it is defined by analytic continuation through the functional equation.

The identities that matter for a spreadsheet.

Γ(z + 1)  =  z · Γ(z)                     (recurrence — the whole function in one line)
Γ(n)      =  (n − 1)!                     for positive integers n
Γ(z)·Γ(1 − z)  =  π / sin(π z)            (reflection — how negatives are reached)
Γ(½)      =  √π
Γ(2z)     =  2^{2z−1} π^{−½} Γ(z) Γ(z + ½)   (Legendre duplication)

Domain and range on the real line. Γ is defined for every real x except x = 0 and the negative integers, where it has simple poles. Between consecutive poles on the negative axis the function alternates sign: Γ is negative on (−1, 0), positive on (−2, −1), negative on (−3, −2), and so on, with |Γ| → ∞ at each pole while the local extrema between poles shrink toward zero as x → −∞. On the positive axis Γ is smooth, strictly positive, and not monotone: it falls from +∞ at 0⁺ to a single minimum near x ≈ 1.4616 (value ≈ 0.8856) and then rises without bound. The two exact fixed points Γ(1) = Γ(2) = 1 straddle that minimum.

Growth. Stirling's series gives ln Γ(x) ~ (x − ½)ln x − x + ½ln(2π) + 1/(12x) − …, so Γ grows faster than any exponential. In double precision Γ(x) overflows a little above x ≈ 171.6; past that the true value exists and is not representable. That single fact — that the useful domain of GAMMA in binary64 is roughly (−∞, 171.6] minus the poles — is why GAMMALN exists as a separate function and why almost all serious use goes through the logarithm instead.

No branch cuts on the real line. Γ is real-analytic on each interval between poles; the branch-cut question belongs to the complex function, which Excel does not expose.

Arguments

Try it

Quirks

Result and edge cases

Returns Number.

• Negative non-integers are in the domain and the values alternate in sign. Reaching them requires the reflection formula, and reflection is where accuracy goes to die; see Numerical notes.
• Very small positive x has Γ(x) ≈ 1/x − γ + …, so the function diverges like a reciprocal. For x below about 10⁻³⁰⁸ the reciprocal itself overflows, which is why an argument at the bottom of the double range produces an overflow rather than a huge finite number.
• Array arguments are lifted elementwise by the module's broadcast helper.

Errors

Whether Excel agrees with any row of that table is unverified here.

How to implement

Numerical notes

The two classical designs. Serious implementations of Γ come in two families. Lanczos (1964) approximates Γ(z + 1) by a shifted product of a power, an exponential, and a short rational series in z, with coefficients tuned to a chosen shift; it is elegant, uniform, and easy to get to roughly working precision. Cody-style minimax rationals — the SPECFUN/Algorithm 715 lineage, and what fdlibm and most libm implementations descend from — reduce the argument onto a small interval by the recurrence and evaluate a minimax rational there, with separate handling for the large-argument Stirling regime. The minimax approach generally wins on last-bit accuracy; Lanczos wins on brevity. Boost.Math documents a Lanczos with extended coefficients as a way to close much of the gap.

A careful implementation therefore does not route through the logarithm for moderate arguments. It uses the recurrence to move x into a small interval around the minimum, evaluates a minimax rational there, and multiplies the recurrence factors back — all in the value domain, where no exponential amplification occurs — reserving Stirling for large x.

Reflection is the second hazard. For x < ½ the standard route is

Γ(x)  =  π / ( sin(π x) · Γ(1 − x) )

and it has three separate problems. First, sin(πx) must be evaluated with the argument reduced exactly, or the answer near a large-magnitude pole is dominated by the reduction error rather than by anything about Γ. Second, near a pole sin(πx) is tiny and the quotient is ill-conditioned in exactly the way a simple pole demands: the condition number is unbounded, so there is a genuine limit to achievable accuracy there, not merely an implementation weakness. Third, the sign must be recovered separately from the magnitude, since the usual formulation computes |Γ| through logs and then reattaches the sign of sin(πx) — which is what the reference engine does.

The floating-point domain is small. Roughly (−∞, 171.6] minus the poles, with the useful positive range ending abruptly. Most computations that reach for Γ (binomial coefficients, beta functions, distribution normalising constants) are ratios whose value is modest even when the factors are not; those computations belong in log space from the start.

Rust code

58 gamma "GAMMA" (1, 1) [V "number"] |s, a, sv| { out(s, sd::gamma_kernel(num(s, a, 0)?)) }

fn is_integer_like(x: f64) -> bool {
    // BUG-FUNC-027 CLASS-A2: a fixed absolute tolerance falsely collapses a tiny
    // non-integer such as -1e-200 onto 0. Test integrality relative to magnitude
    // so only genuine integers (and exact 0) qualify.
    (x - x.round()).abs() <= 1.0e-12 * x.abs()
}

fn has_gamma_pole(x: f64) -> bool {
    x <= 0.0 && is_integer_like(x)
}

pub fn gamma_kernel(x: f64) -> Result<f64, WorksheetErrorCode> {
    if !x.is_finite() || has_gamma_pole(x) || x.is_subnormal() {
        return Err(WorksheetErrorCode::Num);
    }

    // Gamma(n) = (n-1)!; retain the reverse product without a calibrated
    // upper cutoff. Above 171 the factorial exceeds binary64's finite range.
    if x >= 1.0 && x.fract() == 0.0 {
        if x > 171.0 {
            return Err(WorksheetErrorCode::Num);
        }
        let mut product = 1.0;
        for k in (2..x as u32).rev() {
            product *= k as f64;
        }
        return Ok(product);
    }
    // Gamma(n+1/2) = sqrt(pi) * product(k+1/2, k=0..n-1).
    // The seed is RN(sqrt(pi)); the product is formed before multiplying it.
    if x > 0.0 && x.fract() == 0.5 {
        if x > 171.5 {
            return Err(WorksheetErrorCode::Num);
        }
        let mut product = 1.0;
        for k in 0..x as u32 {
            product *= k as f64 + 0.5;
        }
        let value = product * f64::from_bits(0x3ffc_5bf8_91b4_ef6b);
        return if value.is_finite() { Ok(value) } else { Err(WorksheetErrorCode::Num) };
    }

    let value = crate::ported::excel_numeric::gamma_excel(x);
    if !value.is_finite() {
        return Err(WorksheetErrorCode::Num);
    }
    Ok(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.

• XC-GAMMA-001: build 19929 versus build 20131; 1 same-input pairs, 0 differing answers. Compared axes: excel-build. Numeric answers were compared by their recorded binary64 bits; other answers by typed value.

Agreement on these compared inputs does not establish agreement on unmeasured inputs.

Recorded environments and comparison details.

Alternatives

Relationships

• GAMMALN and GAMMALN.PRECISE — the natural logarithm of Γ, restricted to positive arguments. They exist because Γ overflows and lnΓ does not, and they are the right tool for every ratio-of-gammas computation. Note a structural fact recorded in the evidence: the log-gamma kernel identified and landed upstream is wired only to those two surfaces, and deliberately does not change GAMMA. The three surfaces share a module and do not share their numeric path.
• FACT — the factorial for non-negative integers. GAMMA(n+1) and FACT(n) are the same number mathematically; whether they are the same computation, and agree in the last bit, is unestablished. FACT has an exact small-table implementation available to it that GAMMA does not.
• FACTDOUBLE, COMBIN, PERMUT, MULTINOMIAL — combinatorial functions expressible through Γ, and generally better computed without it.
• BETA.DIST / GAMMALN — the beta function B(a,b) = Γ(a)Γ(b)/Γ(a+b) is the standard reason people reach for GAMMA and the standard place they should have reached for GAMMALN instead, because each factor can overflow while the ratio is small.
• Module siblings: ERF, ERF.PRECISE, ERFC, ERFC.PRECISE, WEIBULL, WEIBULL.DIST share the implementing module. Module sharing is an implementation fact and implies nothing semantic — and here it comes with an explicit statement that the kernels differ.

Named variants

No named variants are recorded yet.