FLOOR.MATH in Google Sheets: Round to Lower Multiples and Control Negatives

• Introduced: first launch date or introduction version not established by the official history reviewed.
• Availability: documented in the rolling Google Sheets service; this guide uses ordinary cells in the web editor, without a claim about identical mobile authoring workflows.
• Checked: October 14, 2026 against the official FLOOR.MATH reference. Examples were independently calculated; no native Sheets execution was performed.

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

The number is required. Significance defaults to 1, and Google explicitly says its sign is ignored. For a negative number, mode zero or omission means away from zero; a nonzero mode means toward zero. Official FLOOR.MATH reference

Significance defines the distance between neighboring permitted multiples. It does not say how many digits to display. Mode concerns the negative direction and is independent of a cell’s number format.

=FLOOR.MATH(8.4)
=FLOOR.MATH(-8.4)
=FLOOR.MATH(-8)

Expected results: 8, −9, −8. The value −9 is lower than −8.4, so the negative result moves away from zero. An already integral input stays at its integer value with the default step.

This is not simply removing digits after a decimal separator. Such removal would give −8 for −8.4, which represents a different direction.

=FLOOR.MATH(18.4,3)
=FLOOR.MATH(18.4,-3)
=FLOOR.MATH(-18.4,3)
=FLOOR.MATH(-18.4,-3)

Expected results: 18, 18, −21, −21. Positive and negative significance values with the same magnitude define the same grid in this function.

For an integer off the grid, =FLOOR.MATH(19,3) gives 18. Test that kind of value when a whole-number input is not necessarily divisible by the chosen step.

=FLOOR.MATH(-18.4,3,0)
=FLOOR.MATH(-18.4,3,1)
=FLOOR.MATH(-18.4,3,-1)

Expected results: −21, −18, −18. Both displayed nonzero modes switch the negative result toward zero. The zero case retains the default direction.

Choose the direction from the meaning of the data. For example, a negative adjustment is not automatically suitable for the same grouping convention as a positive available quantity.

For fictional quantities 9, 16, 17 in A2:A4, each complete set requires four pieces. In B2 enter and fill down:

=FLOOR.MATH(A2,4)

Expected pieces allocated to complete sets: 8, 16, 16. In C2 use =B2/4; expected set counts are 2, 4, 4. In D2 use =A2-B2; leftovers are 1, 0, 1.

Here 42 total pieces produce 40 allocated pieces and two leftovers. If processed as a pool, the sample still gives ten sets. That agreement is specific to these quantities.

For two separate quantities 3 and 3, processing each alone produces zero four-piece sets, while pooling six pieces produces one set. Decide whether the records can share stock before using a total-based formula.

For a band width of five, enter −6, −1, 0, 4, 5, 11 in F2:F7 and use:

=FLOOR.MATH(F2,5)

Expected lower boundaries: −10, −5, 0, 0, 5, 10. This defines intervals including their lower boundary and excluding the next one: [−5,0), [0,5), [5,10), and so on.

The −1 input belongs to the band beginning at −5, not the one beginning at zero. A toward-zero negative mode would no longer implement these mathematical lower intervals.

Keep the boundary convention with the result column. The formula creates numbers; it does not decide whether an analysis intends inclusive upper or lower endpoints.

For quarter units, =FLOOR.MATH(9.1,0.25) gives 9 and =FLOOR.MATH(-9.1,0.25) gives −9.25. Adding mode 1 to the second formula gives −9.

INT handles the ordinary integer floor. ROUNDDOWN uses decimal places and a toward-zero rule, so it should not be substituted without checking negative cases.

These controls cover ordinary finite numeric inputs. The source does not specify every coercion, invalid-step or extreme-value error code. Preserve original measurements, validate editable parameters and test the actual threshold values in the target file instead of mistaking formatted appearance for stored precision.

Verification: Expected results use fictional data and independent calculations rather than native Google Sheets observations. See the editorial testing standards.