MROUND

What it does

MROUND(number, multiple)

MROUND(number, multiple) selects a nearest multiple, with mathematical halves away from zero. Both nonzero inputs must have the same sign.

Try it

=MROUND(14,4) returns 16. =MROUND(-14,-4) returns -16. For decimal multiples, Microsoft documents differing midpoint directions: MROUND(6.05,0.1) gives 6, while MROUND(7.05,0.1) gives 7.1.

Quirks

Opposite signs give #NUM!; the current implementation returns zero for a zero multiple. A decimal multiple is already a binary64 approximation before rounding begins. The recorded decision admits a small tolerance below one half, rather than comparing exact decimal distances.

How to implement

After coercion and sign checks, compute q=R(number/multiple), where R is the stored 64-bit-significand operation described for variance. Let c=floor(q) and f=q-c. Increment c when 0.5-f<=5e-15. Return R(c*multiple).

Worked: 14/4=3.5; c=3, f=0.5, so increment to four and multiply by four to obtain sixteen. The same positive quotient with two negative arguments produces a negative multiple. Preserve the division store and final product store, and use the binary64 constant 5e-15 rather than a decimal-string comparison. The adjacent-half probes distinguish the tolerance on the measured inputs. The cited bank contains 50,782 matching rows; no Handbook suite has been published.

Rust code

call(MROUND, "MROUND", 2, MULTIPLE, mround)

/// MROUND: the multiple of `multiple` nearest the number, a half away from zero, counted from the
/// binary64 quotient. Arguments of different signs are #NUM!; a zero multiple gives 0.
fn mround(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    numeric(MROUND, arguments, services, |site, n| {
        let (number, multiple) = (n[0], n[1]);
        if multiple == 0.0 || number == 0.0 {
            Ok(0.0)
        } else if (number < 0.0) != (multiple < 0.0) {
            Err(Exit::Error(WorksheetErrorCode::Num))
        } else {
            nearest_multiple(site, number, multiple)
        }
    })
}

/// The multiple MROUND chooses for `number` and `multiple`, both non-zero and of one sign: the
/// count of multiples is the integer part of their quotient, a division at an arithmetic site,
/// plus one when the quotient's fraction is a half or within [`MROUND_HALF_TOLERANCE`] below it;
/// the result is the count times the multiple, a product at an arithmetic site (dc-j9k). So
/// MROUND(6.05,0.1) is 6, its quotient 60.49999999999999 lying 7.1E-15 below the half, while
/// MROUND(23.499999999999996,1) is 24 and MROUND(-0.04999999999999977,-0.1) is -0.1, as Excel
/// 16.0.20430 gives them (the OxFunc-Evidence bank's half-boundary and fraction-cutoff runs). A
/// quotient beyond binary64's range is #NUM!.
fn nearest_multiple(site: &Site<'_>, number: f64, multiple: f64) -> Step<f64> {
    // Positive, since the signs agree.
    let quotient = site.divide(number, multiple)?;
    if quotient.is_infinite() {
        return Err(Exit::Error(WorksheetErrorCode::Num));
    }
    let whole = decimal::floor(quotient);
    // Exact: the fraction of a binary64, and its distance below a half when it is near one.
    let below_half = 0.5 - (quotient - whole);
    let count = if below_half <= MROUND_HALF_TOLERANCE {
        whole + 1.0
    } else {
        whole
    };
    site.multiply(count, multiple)
}

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-MROUND-001: build 19929 versus build 20430; 3 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.

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

ROUND chooses decimal places; CEILING.MATH and FLOOR.MATH choose a directional multiple.

Named variants

No named variants are recorded yet.