MOD

What it does

MOD(number, divisor)

MOD(number, divisor) returns the floored remainder. For ordinary nonzero remainders the result follows the divisor’s sign. Noninteger inputs are allowed. The mathematical identity is number-divisor*INT(number/divisor).

Try it

=MOD(-7,3) returns 2: -7 = (-3)*3+2. =MOD(7,-3) returns -2. With binary64 decimal inputs, the recorded MOD(10,0.1) is 0.09999999999999945, not zero.

Quirks

A zero divisor gives #DIV/0!. Recorded Excel returns #NUM! when the rounded quotient magnitude reaches 1.1259e12, although a mathematical remainder still exists. Tiny operands can return the wrong sign-adjusted magnitude or #NUM!; these are compatibility rules, not properties of mathematical modulo. The prior explanation that Excel necessarily subtracts two large approximate products is not supported by the later exact-remainder evidence.

How to implement

Use R(op) as defined in the stored arithmetic recipe.

1. Coerce both arguments, reject a zero divisor, and return zero for a zero dividend. Compute q=R(number/divisor); reject abs(q)>=1.1259e12 or a nonfinite quotient.
2. Compute the truncating binary64 remainder r, using a correctly implemented fmod primitive or integer significand/exponent reduction. Do not compute number-divisor*floor(q) in floating point: that can lose the small remainder.
3. If r is nonzero and has the divisor’s opposite sign, normally set v=R(r+divisor). If that addition rounds exactly to the divisor, the current compatibility rule returns zero.
4. Before that addition, a subnormal r of the opposite sign needs a special branch. With a=abs(divisor) and ordinary binary64 operations, set c=(2^27+1)*a, low=a-(c-(c-a)). A subnormal low, or abs(q)>=2^26, gives #NUM!. Otherwise, if abs(r)<2^-1026, return R(a+abs(r)) with the divisor’s sign. A larger remainder takes the normal addition.
5. If no sign adjustment is needed, keep r. Any subnormal final result is #NUM!.

Worked ordinary path: fmod(-7,3)=-1; the signs differ, so -1+3=2. Worked large-quotient refusal: MOD(1125899999999,1) is recorded as zero, while MOD(1125900000000,1) is #NUM!.

To implement the truncating remainder without a host fmod, decode the positive magnitudes as Mx*2^Ex and My*2^Ey, with integer significands. Set E=min(Ex,Ey), A=Mx << (Ex-E) and B=My << (Ey-E). Take the integer remainder U=A mod B, then restore the dividend's sign to U*2^E. Arbitrary-size integers make the shifts and remainder exact. This is a separate calculation from the rounded quotient used by the rejection test; do not reuse that quotient to reconstruct the remainder.

For 10 and binary64 0.1, choose the common unit 2^-56. The integers are A=720575940379279360 and B=7205759403792794. Integer division gives quotient 99 and remainder 7205759403792754. Rescaling that remainder gives 0.09999999999999945. Rounding the quotient to 100 before subtracting would erase the answer this function records.

Worked tiny path: inputs -1.223247290044572e-296 and 1.223247290044539e-296 have an opposite-sign subnormal remainder. The recorded special branch adds its magnitude and returns 1.223247290044572e-296, larger than the divisor. The mathematically correct remainder is 1.2232472900445062e-296. The note retains input and result bits.

The rule matches the cited recorded bank and fresh probes. Interpreting the split as evidence of a Dekker product is an inference; the measured rule does not reveal Microsoft’s private implementation.

Rust code

call(MOD, "MOD", 2, DIVISOR, modulo)

/// MOD: number - divisor * INT(number / divisor), with the sign of the divisor, as Excel computes
/// it (dc-j9k): the exact binary64 remainder of the number by the divisor (`%`, which rounds
/// nothing), plus the divisor once when the remainder's sign differs from the divisor's, an
/// addition at an arithmetic site. So MOD(10,0.1) is 0.09999999999999945 and MOD(-5.3,1) is
/// 0.7000000000000002, as Excel gives them (the catalogue oracle's sheet dcj9k_math: 124 of 124
/// readings). A zero divisor is #DIV/0!, and a quotient of magnitude 1.1259E+12 or more is #NUM!
/// (Excel: MOD(1125899999999,1) is 0 and MOD(1125900000000,1) is #NUM!). A remainder below the
/// normal range that needs the divisor added follows `tiny_remainder`.
fn modulo(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    numeric(MOD, arguments, services, |site, n| {
        let (number, divisor) = (n[0], n[1]);
        if divisor == 0.0 {
            return Err(Exit::Error(WorksheetErrorCode::Div0));
        }
        if number == 0.0 {
            return Ok(0.0);
        }
        let quotient = site.divide(number, divisor)?;
        if !(quotient.abs() < MOD_QUOTIENT_LIMIT) {
            return Err(Exit::Error(WorksheetErrorCode::Num));
        }
        let remainder = number % divisor;
        let result = if remainder != 0.0 && (remainder < 0.0) != (divisor < 0.0) {
            if remainder.is_subnormal()
                && let Some(result) = tiny_remainder(site, remainder, divisor, quotient)?
            {
                return Ok(result);
            }
            let adjusted = site.add(remainder, divisor)?;
            // A remainder the divisor absorbs is 0 (dc-bd5: MOD(-5,9.99999999999999E+307) is 0
            // in Excel 16.0.20430).
            if adjusted == divisor { 0.0 } else { adjusted }
        } else {
            remainder
        };
        // The recorded tiny MOD banks return #NUM! when the final remainder is subnormal.
        if result.is_subnormal() {
            Err(Exit::Error(WorksheetErrorCode::Num))
        } else {
            Ok(result)
        }
    })
}

/// A subnormal `remainder` whose sign differs from the `divisor`'s. A divisor whose Veltkamp low
/// part (the bits a split at 2^27 + 1 leaves below its high half) is itself subnormal gives #NUM!,
/// as does a quotient of 2^26 or more; the split's two parts are what a Dekker product of the
/// count and the divisor multiplies, and these are the cases where that product leaves the normal
/// range. Otherwise a remainder below 2^-1026 is added to the divisor's magnitude instead of taken
/// from it, and a larger one (`None`) takes the ordinary step. So MOD(-1.0000000000000001E-296,
/// 1E-296) is 1.0000000000000001E-296 and the same at 1E-300 is #NUM!, as Excel 16.0.20527 gives
/// them. This rule matches all 152,395 bank rows with normal operands and 1,128 new readings.
fn tiny_remainder(
    site: &Site<'_>,
    remainder: f64,
    divisor: f64,
    quotient: f64,
) -> Step<Option<f64>> {
    let magnitude = divisor.abs();
    let scaled = VELTKAMP_SPLIT * magnitude;
    let low = magnitude - (scaled - (scaled - magnitude));
    if low.is_subnormal() || quotient.abs() >= MOD_DEKKER_COUNT {
        return Err(Exit::Error(WorksheetErrorCode::Num));
    }
    if remainder.abs() >= SMALLEST_DISTANCE {
        return Ok(None);
    }
    let sum = site.add(magnitude, remainder.abs())?;
    Ok(Some(if divisor < 0.0 { -sum } else { sum }))
}

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.

Retained controls from the indexed captures:

These are earlier recorded answers, not new Excel runs or an additional cross-build comparison. The function JSON retains each source locator, exact argument bits where available, and input-route status. Call notation does not imply that captured binary64 values were entered as decimal formula literals.

Alternatives

QUOTIENT truncates the quotient, so pairing it with MOD does not reconstruct mixed-sign inputs. INT supplies the floor convention; a language’s % may use another convention.

Named variants

No named variants are recorded yet.