FLOOR.MATH

What it does

FLOOR.MATH(number, [significance], [mode])

FLOOR.MATH(number, [significance], [mode]) rounds number down to a multiple of significance, with mode selecting what "down" means for negative numbers.

The mathematics is one line. For a nonzero significance s, the mathematical floor to a multiple is

floor_to_multiple(x, s) = ABS(s) · floor( x / ABS(s) )

which is the largest multiple of ABS(s) not exceeding x. That is the toward--∞ reading, and it is what Microsoft documents as the default: a positive number with a fractional part loses it, and a negative number moves further from zero.

The mode argument selects the other reading for negative arguments — the toward-zero truncation:

trunc_to_multiple(x, s) = sign(x) · ABS(s) · floor( ABS(x) / ABS(s) )

The two agree on every non-negative argument and on every exact multiple. They differ only on negative arguments that are not multiples, where one gives the mathematical floor and the other gives truncation toward zero.

Arguments

The pinned catalogue supplies FLOOR.MATH(number, [significance], [mode]): one required argument and two optional arguments.

Try it

Quirks

Result and edge cases

Returns Number.

• significance omitted — documented default 1, so the function is a plain floor.
• significance negative — the absolute value is used; the sign of the significance does not flip the direction. This is the design difference from the legacy FLOOR, which errors when the two signs disagree.
• significance zero — not documented on this page. The mathematical expression divides by it. FLOOR.PRECISE's page documents that a zero in either position gives zero; whether FLOOR.MATH shares that convention is unchecked.
• number zero — zero is a multiple of everything, so the answer is zero.
• Exact multiples — unchanged, in both modes.
• Very large number — above 2^53 the spacing of doubles exceeds 1, so with the default significance the function is the identity; with a small significance it still is, because no multiple of a small s lies strictly between two adjacent doubles up there.
• Non-integer significance — the interesting case, and the one where the answer depends on arithmetic rather than on the rule. See the numerical notes.

Errors

Microsoft's FLOOR.MATH page documents no error conditions. That is itself notable: the legacy FLOOR documents #NUM! for mismatched signs, and FLOOR.MATH was introduced precisely to remove that restriction. Legacy FLOOR also gives #DIV/0! for a zero significance with a nonzero number (FN-FLOOR-001), which Microsoft's pages do not say. What a zero significance does in FLOOR.MATH the page does not say, and it is untested.

Non-numeric arguments surface #VALUE! under the shared call model, and error values propagate. The mechanically rendered battery beside this page shows the boundary probes.

How to implement

Numerical notes

The rule is exact arithmetic; the implementation is not, and the gap between them is this page's real content.

The naive op-graph fails, and it fails visibly. Computing s · floor(x / s) in binary64 performs two roundings around an exact operation:

1. x / s rounds. If the true quotient is an integer but the rounded quotient falls a hair below it, floor drops an entire step.
2. floor is exact — an IEEE 754 roundToIntegral operation.
3. s · q rounds again, so the result need not be an exact multiple of s even when the mathematics says it is.

Step 1 is the damaging one, and it is not a rare edge case. Working the naive op-graph through in binary64 (this is arithmetic, not a claim about Excel):

In each row the naive route returns a value one whole step too low, because the decimal significance is not a binary64 value and the quotient of two rounded decimals lands just under the integer it should be. A user who thinks of the significance as "cents" or "tenths" sees the function skip a tread. This is the classic complaint about every floor-to-a-multiple function in every spreadsheet, and it is arithmetic rather than a defect in the rule.

What a careful implementation does about it. There is no single accepted answer, and the choice is a decision to record rather than a neutral one:

• Exactness-preserving route: use a fused multiply-add to compute the residual x - s·q exactly and correct q by one when the residual has the wrong sign. This makes the result the true floor of the represented values x and s, which is the defensible mathematical position, and it still returns 0.28 for the first row above — because 0.29/0.01 genuinely is less than 29 in the values the worksheet holds.
• Intent-preserving route: apply a relative tolerance, treating a quotient within a few ulp of an integer as that integer. This returns 0.29 and matches what users expect, at the cost of no longer being a mathematical function of its inputs — there are then arguments where FLOOR.MATH(x, s) > x.

These two routes disagree on a set of inputs that is easy to enumerate, and the Handbook has not determined which one Excel takes. That is the most valuable open question on this page, because the answer changes results in the second decimal place of ordinary financial formulas, not in the last bit.

Power-of-two significances. When s is a power of two, x/s and s·q are both exact in the naive op-graph. That does not show such a significance is safe: FLOOR's readings refute it (FN-FLOOR-002 and FLOOR), and FLOOR.MATH itself is untested.

A note on mode and negative zero. In the toward-zero reading, a negative argument in (-s, 0) maps to zero. Whether that zero carries a sign is not documented and is visible through 1/FLOOR.MATH(-0.5, 1, 1).

Rust code

3 floor_math "FLOOR.MATH" (1, 3) [V "number", V "significance", V "mode"] |s, a, sv| {
    three(s, a, cf::floor_math_kernel)
}

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.PRECISE — the same function without the mode argument, documented as always returning the mathematical floor irrespective of both signs. It should therefore equal FLOOR.MATH with mode omitted, on every input. Whether it does bitwise — including at a zero significance, where only FLOOR.PRECISE's page documents an answer — is the natural cross-surface probe.
• FLOOR — the legacy member. It rounds toward zero for negative numbers with negative significance and errors when the signs of the two arguments differ. Replacing FLOOR with FLOOR.MATH in an existing workbook changes results on negative arguments and removes an error; that is a migration hazard, not a bug fix.
• CEILING.MATH, CEILING.PRECISE, CEILING, ISO.CEILING — the upward mirror of the same family, sharing the implementing module. ISO.CEILING and CEILING.PRECISE are the toward-+∞ pair.
• INT — INT(x) is the floor to integers, toward -∞, and coincides with FLOOR.MATH(x) at the default significance in the default mode. TRUNC(x) is the toward-zero reading and coincides with the other mode.
• MROUND — nearest multiple rather than lower multiple; it disagrees with this function on the upper half of every step.
• ROUNDDOWN — toward zero to a digit position rather than to a multiple; the same direction convention as the non-default mode, on a decimal grid.
• EVEN — a fixed-step rounding away from zero, for contrast: the worksheet's rounding functions do not share one direction convention, and this family is where the convention is made an explicit argument.

Named variants

No named variants are recorded yet.