FLOOR

What it does

FLOOR(number, significance)

FLOOR(number, significance) rounds number down to a multiple of significance. The mathematical definition is one line:

FLOOR(x, s) = s × floor(x / s)

The floor is taken on the quotient, not on the number, and that one fact sets the direction for every sign combination. A quotient of 1.25 floors to 1 and a quotient of −1.25 floors to −2, so the same rule rounds toward zero in some cases and away from it in others:

The first, second and fourth rows are Microsoft's own examples. The third follows from the definition and from Microsoft's VBA reference, which says a positive significance rounds a negative number away from zero; all 292 recorded readings with that sign pattern fit it.

That definition is not quite what Excel evaluates. In recorded readings Excel rounds the quotient to 15 significant decimal digits before it takes the floor:

FLOOR(x, s) = s × floor( x / s rounded to 15 significant digits )

The difference is small in size and large in effect, because a quotient that lands just under an integer is treated as that integer. FLOOR(1938.84, 9.06) is 1938.8400000000001 in Excel, where the plain definition in binary64 gives 1929.7800000000002, one whole step lower. Numerical notes below gives the counts and the limits of that statement.

Domain and range: all real pairs except those in Errors. The result is a multiple of significance as far as the final multiplication can represent one. It is usually at or below number for a positive quotient, but not always, as the example above shows.

Arguments

• Both arguments are required. The function has an arity of exactly two. Its modern relatives take significance as optional with a default of 1; FLOOR does not.
• significance is a multiple, not a number of digits. FLOOR(1.58, 0.1) is 1.5.

Try it

Quirks

Result and edge cases

Returns Number.

• number is zero. The notes below record FLOOR(0, 0) and FLOOR(-0, 0) as +0, and a zero number with a negative significance as +0 rather than #NUM!. A zero number is settled before either error can arise.
• significance is zero with a nonzero number gives #DIV/0!.
• number is already a multiple. Microsoft's VBA reference says no rounding occurs. For ordinary sizes that holds, but it is not true at every size: see the large-quotient readings in Numerical notes, where FLOOR(4503599627370496, 2) moves the number.
• A tiny negative number with a positive significance. The rule gives one step below zero, and FLOOR(-1E-300, 2) is -2 in the recorded readings. One reading is an exception: FLOOR(-2.2250738585072014E-308, 2) is 0, where the quotient has fallen into the subnormal range.
• The largest double. FLOOR(1.7976931348623157E+308, 0.1) and the same number with significance 2 both give #NUM!, and so does its negative at significance 0.1, 2, -0.1 and -2.

Errors

Neither Microsoft page read for this entry says what a zero significance does. The readings above are Excel 16.0 build 20430, Windows 64-bit, with the CPU not recorded.

Microsoft's support page explains the #NUM! example as "different signs", and its remarks say a negative number is rounded away from zero. Neither sentence is general. FLOOR(-2.5, 2) has different signs and returns a number, and FLOOR(-2.5, -2) in the same page's example table rounds toward zero. The VBA reference states the cases correctly, and the table under What it computes follows it.

How to implement

Numerical notes

The notes below cover zero. This section covers what the plain definition misses everywhere else.

The plain route fails visibly. s × floor(x / s) in binary64 rounds twice around one exact step. x / s rounds, and when the true quotient is an integer the rounded one can land just below it, which costs a whole step. The floor is exact. The product s × q rounds again, so the answer need not be a multiple of the represented s.

Excel does not take the exact floor of the stored values either. A careful implementation might compute the true floor of the two binary64 values. Excel does not. 0.1 is stored slightly above one tenth, so the exact quotient of 0.5 by it is slightly below 5 and its exact floor is 4. Excel's FLOOR(0.5, 0.1) is 0.5.

The 15-digit quotient. Rounding x / s to 15 significant decimal digits, then taking the floor, then multiplying by s in binary64, matches Excel on more rows than either alternative:

The 945 in the last column counts a subnormal quotient or number as zero. Those rows are part of a set of 1,335 readings, and the rule matches all 1,335 once the error rows of Errors and the zero rows of the notes below are added to it. All of these counts were recomputed for this page from the recorded inputs. None is a held-out result: the rule was first stated from readings of this family, and the same readings are used here. The two sets hold different inputs, so they say nothing about a difference between the two builds.

What the rule explains, each from a recorded reading:

The last row is the unintuitive one. Once the quotient has more than 15 significant digits the rounding step moves it by far more than one unit, and the answer changes though the number was already a multiple. A power-of-two significance gives no protection, although both the division and the multiplication are exact there. Among the recorded readings the rule also holds at very large quotients, where the answer can differ from number in the sixteenth digit.

Everyday consequence. For prices and quantities in ordinary ranges the 15-digit step does what a user wants: a quotient of 28.999999999999996 counts as 29. The price is that the result can sit one unit above number, so FLOOR(x, s) <= x is not safe as a test in a formula.

Rust code

call(FLOOR, "FLOOR", 2, SIGNIFICANCE, floor)

/// FLOOR: the number rounded down to a multiple of the significance; for a negative number and a
/// positive significance down is away from zero, and for both negative it is toward zero. A zero
/// significance is #DIV/0! for a nonzero number; zero always gives positive zero. A positive
/// number with a negative significance gives #NUM!.
fn floor(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    numeric(FLOOR, arguments, services, |site, n| {
        let (number, significance) = (n[0], n[1]);
        if number == 0.0 {
            Ok(0.0)
        } else if significance == 0.0 {
            Err(Exit::Error(WorksheetErrorCode::Div0))
        } else if number > 0.0 && significance < 0.0 {
            Err(Exit::Error(WorksheetErrorCode::Num))
        } else {
            to_multiple(site, number, significance, Toward::Down)
        }
    })
}

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.

Alternatives

Relationships

• FLOOR.MATH takes the absolute value of the significance, so signs can no longer disagree, and adds a mode argument for negative numbers. Moving a workbook from FLOOR to FLOOR.MATH changes answers: FLOOR(-2.5, -2) is -2, and the default reading of FLOOR.MATH as Microsoft describes it moves the same arguments further from zero.
• FLOOR.PRECISE also ignores the sign of the significance, and is the replacement Microsoft's VBA reference names.
• CEILING is the upward mirror in the same implementing module.
• MROUND rounds to the nearest multiple. The notes record that it too treats a quotient very close to a half as that half, so the worksheet's multiple functions share the idea of cleaning up a quotient, though the notes do not establish a shared routine.
• INT is the floor to integers, toward minus infinity. FLOOR(x, 1) is the same idea, but do not assume the same answer next to an integer: FLOOR(1*(1-2^-51), 1) is 1.
• TRUNC and ROUNDDOWN round toward zero at a decimal digit position rather than at a multiple.

Named variants

No named variants are recorded yet.