qax-os/excelize · error

#NUM!

#NUM!

Error message

#NUM!

What it means

This site is the tail of the inverse standard normal CDF implementation (used by NORMSINV/NORM.INV/NORMDIST-style functions). It covers only three probability regions; if p is outside (0,1) (p <= 0, p >= 1, or NaN), the rational approximations do not apply and the function returns errors.New(formulaErrorNUM), i.e. #NUM!, matching Excel's #NUM! for invalid numeric arguments.

Source

Thrown at calc.go:10240

		return (((((c[1]*q+c[2])*q+c[3])*q+c[4])*q+c[5])*q + c[6]) /
			((((d[1]*q+d[2])*q+d[3])*q+d[4])*q + 1), nil
	} else if pLow <= p && p <= pHigh {
		// Rational approximation for central region.
		q := p - 0.5
		r := q * q
		f1 := ((((a[1]*r+a[2])*r+a[3])*r+a[4])*r + a[5]) * r
		f2 := (b[1]*r + b[2]) * r
		f3 := ((math.Nextafter(f2, f2)+b[3])*r + b[4]) * r
		f4 := (math.Nextafter(f3, f3) + b[5]) * r
		return (math.Nextafter(f1, f1) + a[6]) * q /
			(math.Nextafter(f4, f4) + 1), nil
	} else if pHigh < p && p < 1 {
		// Rational approximation for upper region.
		q := math.Sqrt(-2 * math.Log(1-p))
		return -(((((c[1]*q+c[2])*q+c[3])*q+c[4])*q+c[5])*q + c[6]) /
			((((d[1]*q+d[2])*q+d[3])*q+d[4])*q + 1), nil
	}
	return 0, errors.New(formulaErrorNUM)
}

// kth is an implementation of the formula functions LARGE and SMALL.
func (fn *formulaFuncs) kth(name string, argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 2 arguments", name))
	}
	array := argsList.Front().Value.(formulaArg).ToList()
	argK := argsList.Back().Value.(formulaArg).ToNumber()
	if argK.Type != ArgNumber {
		return argK
	}
	k := int(argK.Number)
	if k < 1 {
		return newErrorFormulaArg(formulaErrorNUM, "k should be > 0")
	}
	var data []float64
	for _, arg := range array {

View on GitHub (pinned to f2483381fb)

Solutions

  1. Clamp p into an open interval, e.g. =NORMSINV(MIN(MAX(p,0.0000001),0.9999999))
  2. Compute the percentile with a guard: =IF(OR(p<=0,p>=1),NA(),NORMSINV(p))
  3. Fix upstream data so probabilities are strictly between 0 and 1
  4. Catch the Calculate error and treat #NUM! as out-of-domain input in your application

Example fix

// before: =NORMSINV(A1) where A1 = 1 -> #NUM!
// after: =NORMSINV(MIN(MAX(A1,0.0000001),0.9999999))
Defensive patterns

Strategy: validation

Validate before calling

p, _ := strconv.ParseFloat(cellValue, 64)
if !(p > 0 && p < 1) {
    return fmt.Errorf("probability %v out of (0,1); NORMSINV would return #NUM!", p)
}

Try / catch

if _, err := f.Calculate(); err != nil {
    if strings.Contains(err.Error(), "#NUM!") {
        // out-of-domain statistical input; clamp or skip
    }
    return err
}

Prevention

When it happens

Trigger: Calling NORMSINV(p)/NORM.INV(...) (directly or via related statistical functions) with probability p <= 0, p >= 1, or a non-numeric argument coerced to an out-of-range value.

Common situations: Computing z-scores from percentile columns where a cell is 0 or 1 exactly (e.g. 100%); cumulative probabilities computed with floating-point overshoot to 1.0000000001; empty cells coerced to 0 then passed as p.

Related errors

AI-assisted analysis of qax-os/excelize@f2483381fb (2026-09-02). Data as JSON: /api/errors/2fb51ec8d3e8f590. Report an issue: GitHub.