ROUND

What it does

ROUND(number, num_digits)

ROUND(number, num_digits) rounds to the nearest decimal position, with final decimal halves away from zero. Positive digit counts address places after the decimal point, zero the integer position, and negative counts places to its left.

Try it

=ROUND(-7.25,1) returns -7.3; =ROUND(725,-1) returns 730. A final half goes away from zero, including for negative values.

Quirks

The recorded functions first read fifteen significant decimal digits. Asking to retain more digits can therefore change an input that mathematically needs no rounding. For example, the recorded ROUND(-1594628480062905,14) returns -1594628480062900. Huge digit counts have recorded integer-conversion behaviour rather than a simple “keep everything” rule. Near the normal-range boundary and overflow, worksheet publication needs separate handling.

Near the largest finite input, the recorded decimal output can carry NaN bits while Excel classifies the cell as a number. The overflow model and its save/reopen behavior are separate from ordinary IEEE infinity publication. See the packing rules and worked encodings.

How to implement

The current worksheet wrapper reads numeric arguments in order and flushes subnormal numeric operands to zero. Return zero for a zero number before finding its decimal exponent. The following steps start with a finite, nonzero admitted input. The stored-arithmetic chapter explains exact binary decoding and significand rounding.

First construct the number’s fifteen-significant-digit reading. Extract the exact binary64 value and find its decimal exponent e by comparisons with exact powers of ten, so 10^e <= abs(x) < 10^(e+1). Put s=14-e. When abs(s)<=27, evaluate abs(x)*10^s (or abs(x)/10^(-s)) with a 64-bit significand and extended exponent range, rounding to nearest/even. Do not store that scaled value to binary64 before the integer decision. Round it to an integer: exact halves go down for ROUND’s reading, up for the directed functions’ readings. Retain the sign and the exact decimal value integer*10^(-s).

This reading step is a measured reconstruction with a known counterexample even inside the stated scale range. For abs(s)>27, Excel’s scaling is still open; the current code falls back to exact binary64-to-decimal rounding to fifteen digits. That fallback is a candidate, not an identified Excel stage.

1. Truncate num_digits toward zero, clamp to [-2147483647,2147483647], and call the integer D.
2. Compute k=D+e+1 with signed 32-bit wrapping. A negative k returns zero; k>=15 keeps the reading.
3. Otherwise round the exact decimal reading at unit 10^(-D), choosing the larger magnitude at a half. Convert the resulting decimal to nearest binary64. Preserve the original input if its exact value equals the whole reading.
4. A final subnormal result is flushed to zero. For decimal overflow, apply the half-value and encoded-exponent rule. A host without the tagged worksheet-number representation must retain an explicit refusal.

Worked: x=-7.25 has e=0, s=14; its reading is exactly -7.25. For D=1, k=2. At unit 0.1 the magnitude is 72.5 units, so choose 73 and return the binary64 value for -7.3. For a count-overflow control, ROUND(1.5,2147483647) wraps k negative and returns zero on the recorded build.

Rust code

call(ROUND, "ROUND", 2, DIGITS, round)

/// ROUND: to num_digits decimal places (left of the point when negative), a half away from zero.
fn round(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    numeric(ROUND, arguments, services, |site, n| {
        nearest_digits(site, n[0], n[1])
    })
}

/// ROUND's kernel. num_digits counts as a 32-bit integer, toward zero and limited to
/// +/-2147483647, and ROUND keeps `digits + e + 1` significant digits of the number, e being its
/// decimal exponent, the sum taken in 32-bit two's complement: fifteen or more keep the whole
/// reading, fewer than none give 0, and otherwise the reading is rounded at 10^-digits. So
/// ROUND(1.5,2147483647) is 0 (the sum wraps negative) where 2147483646 leaves 1.5, and
/// ROUND(-5.480222088275654E-221,-2147483480.2228494) is -5.48022208827565E-221 (the sum wraps
/// positive), as Excel 16.0.20430 gives them (the OxFunc-Evidence bank's integer-threshold,
/// hyperbolic and count-refinement runs).
fn nearest_digits(site: &Site<'_>, number: f64, places: f64) -> Step<f64> {
    if number == 0.0 {
        return Ok(0.0);
    }
    let limit = f64::from(i32::MAX);
    // An integer within +/-2147483647, so the conversion is exact.
    let digits = decimal::truncate(places).clamp(-limit, limit) as i32;
    let kept = digits
        .wrapping_add(ExactDecimal::binary(number).magnitude())
        .wrapping_add(1);
    if kept < 0 {
        return Ok(0.0);
    }
    let reading = reading(number, false);
    // Without a wrap, kept from 0 to 14 bounds digits to within 340 of 0.
    match reading.round_at(-digits.clamp(-PLACES, PLACES), Direction::Nearest) {
        Some(rounded) if kept < 15 => Ok(flush(stored(site, &rounded)?)),
        _ => whole_reading(site, number, &reading),
    }
}

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

ROUNDDOWN truncates the decimal reading toward zero; ROUNDUP rounds it away from zero. MROUND chooses a multiple. INT floors negatives; TRUNC removes their fraction toward zero.

Named variants

No named variants are recorded yet.