FLOOR.MATH Function
Rounds a number down to the nearest integer or specified multiple.
Summary
The Excel FLOOR.MATH function rounds a number down to the nearest integer or, when a significance is supplied, down to the nearest multiple of that significance. For positive numbers, the result always remains at or below the original value, making it essential when calculations must not exceed available thresholds or capacity limits.
FLOOR.MATH is useful in operational modeling where fractional amounts cannot be counted as complete units. It is frequently applied in inventory packaging, timecard tracking, pricing tiers, and budget batching where workbooks must prevent overstating what is available, finished, or billable.
Purpose
Use FLOOR.MATH when the result must stay on the lower side of a chosen step boundary.
Return Value
Returns the nearest valid multiple at or below the input for positive numbers, adjusted by significance and mode.
Syntax
=FLOOR.MATH(number, [significance], [mode])Microsoft documents one required argument and two optional ones. number is the value to round. significance sets the multiple step size (default is 1), and mode controls the direction used for negative numbers.
Arguments
- number — [required] The numeric value to round down.
- significance — [optional] The multiple to which the value should be rounded. If omitted, Excel defaults to 1.
- mode — [optional] Controls direction for negative numbers (0 rounds away from zero, any non-zero value rounds toward zero).
FLOOR.MATH vs Other Functions
| Function | Direction | Typical Output | Use When |
|---|---|---|---|
FLOOR.MATH |
Downward to multiple | Lower multiple of step | Counting completed units, capacity floors, and downward step boundaries |
CEILING.MATH |
Upward to multiple | Upper multiple of step | Calculating required units, minimum containers, and upward step boundaries |
MROUND |
Nearest multiple | Closest multiple of step | Neutral step-based rounding with no directional bias |
ROUNDDOWN |
Toward zero by decimals | Truncated decimal digits | Digit-based downward rounding rather than step multiple rounding |
Using FLOOR.MATH
FLOOR.MATH is appropriate when the result should align with discrete increments but must never cross above the calculated amount. That makes it suitable for inventory, scheduling, allocation, and capacity calculations where only complete lower-step units are valid.
The significance argument is what distinguishes FLOOR.MATH from simpler functions such as INT or ROUNDDOWN. Instead of working only at decimal positions or integer levels, FLOOR.MATH can operate on arbitrary steps such as 5, 12, 15, or 0.25. This gives it a central role in spreadsheet models: it enforces operational units rather than purely visual decimal formatting.
Negative numbers introduce a further layer of control. Microsoft notes that negative values are rounded away from zero by default, but the mode argument can reverse that behavior toward zero. That matters when the data includes offsets, balances, or signed quantities rather than only positive counts.
Example 1 — Round Down to the Previous Integer
This is the default integer behavior when significance is omitted.
=FLOOR.MATH(B2)If cell B2 contains 6.9, the formula returns 6. The fractional portion is discarded without midpoint rounding, moving the value strictly to the lower whole integer.
In cell D2, round the value down to the nearest whole number.
Example 2 — Round Down to the Nearest Multiple of 5
Supplying a significance turns the function into multiple-based rounding.
=FLOOR.MATH(B3,5)If cell B3 contains 28, the formula returns 25. This is useful when data needs to be grouped into fixed bucket sizes or packaged intervals.
In cell D3, round the value down to the nearest multiple of 5.
Example 3 — Count Complete Packs
This represents a standard operational packaging calculation.
=FLOOR.MATH(B4/C4)If cell B4 contains 47 units and cell C4 defines a pack size of 12, dividing the two yields approximately 3.91. FLOOR.MATH rounds this down to 3, ensuring incomplete packs are excluded.
In cell D4, divide the quantity by the pack size and round down to complete packs.
Example 4 — Use a Dynamic Step Size
The significance does not need to be hardcoded in the formula.
=FLOOR.MATH(B5,C5)If cell B5 contains 93 and cell C5 contains a step size of 25, the formula returns 75. Driving the significance from another cell allows flexible step rounding across different scenarios.
In cell D5, round the value down using the step size stored in the sheet.
Because FLOOR.MATH is multiple-based, it is often more transparent than nested combinations of division and integer truncation. It states the rounding rule directly in the formula rather than leaving the reader to infer it from intermediate arithmetic.
- Omitting significance defaults the function to integer rounding.
- The significance controls the allowed step size.
- The mode argument matters mainly when negative numbers are present.
Conclusion Recap
FLOOR.MATH is the downward counterpart to CEILING.MATH. Use it when the result must stay at or below the lower valid boundary, such as complete packaging units, finished billable hours, or budget allocations that cannot exceed the current cap.
Across the examples, the core pattern remains consistent: round down to a whole integer, round down to a specific multiple, or use cell references to drive dynamic step sizes.
- Summary:
FLOOR.MATHrounds a number down to an integer or specified multiple. - Main use: Lower-bound, completed-unit, and step-based downward rounding.
- Key distinction: It rounds by multiples rather than by decimal positions.
- Negative-number behavior: The mode argument controls direction for negative values.
- Function choice: Use FLOOR.MATH when the rule is downward to the previous valid step.
Share This Lesson
Spread the word with your peers and challenge your friends