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 values, this means the result stays at or below the original number.
FLOOR.MATH is useful when the calculation should not overstate what is available, complete, or billable. That often makes it a better fit than standard rounding in operational contexts such as pack counts, completed time blocks, and threshold-based quantities.
FLOOR.MATH is the downward version of that same idea. It helps when a workbook needs to push a value down to the nearest allowed step, which is useful in budgeting, batching, quotas, and other rules where you cannot exceed the current threshold.
Purpose
Use FLOOR.MATH when the result must stay on the lower side of a chosen boundary.
Return Value
Returns the nearest valid result at or below the input for positive values, subject to the significance and mode arguments.
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, and mode controls the direction used for negative numbers.
Arguments
- number - [required] The value to round down.
- significance - [optional] The multiple to which the value should be rounded. If omitted, Excel uses 1.
- mode - [optional] Controls whether negative numbers are rounded toward or away from zero.
FLOOR.MATH vs Related Functions
| Function | Direction | Best Fit |
|---|---|---|
FLOOR.MATH |
Downward to an integer or multiple | Completed units, lower bounds, and step-based downward rounding |
CEILING.MATH |
Upward to an integer or multiple | Required units, minimum capacity, and step-based upward rounding |
MROUND |
Nearest multiple | Neutral step-based rounding with no fixed directional bias |
ROUNDDOWN |
Toward zero at digit positions | Digit-based downward rounding rather than multiple-based rounding |
Using FLOOR.MATH
FLOOR.MATH is appropriate when the result should align with discrete increments but should not 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 the integer level, FLOOR.MATH can operate on arbitrary steps such as 5, 12, 15, or 0.25. This gives it a different role in spreadsheet models: it is often used to enforce operational units rather than display precision.
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.
=FLOOR.MATH(B1)If B1 contains 6.9, the result is 6. The fractional portion is not evaluated for midpoint logic; the result simply moves to the lower integer.
In cell D2, round the value down to the nearest whole number.
Example 2 - Round Down to the Nearest Multiple of 5
A supplied significance turns the function into multiple-based rounding.
=FLOOR.MATH(B2,5)If B2 contains 28, the result is 25. This is useful when the data needs to be expressed in fixed steps rather than in arbitrary units.
In cell D3, round the value down to the nearest multiple of 5.
Example 3 - Count Complete Packs
This is a typical operational use case.
=FLOOR.MATH(B3/C3)If B3 contains 47 units and C3 contains a pack size of 12, the quotient is slightly below 4, and FLOOR.MATH returns 3. The partial pack is excluded because it is not a complete dispatch unit.
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.
=FLOOR.MATH(B4,C4)If B4 contains 93 and C4 contains 25, the result is 75. This pattern is useful when different scenarios or categories use different valid increments.
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 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 opposite kind of step rounding. You use it when the result should stay at the lower valid step, such as complete packs, finished blocks, or any amount that should not be rounded too high.
Across the examples, the pattern stayed simple: round down to a whole number, round down to a chosen multiple, or use a cell to control the step size. If the worksheet should count only what is fully available or fully complete, FLOOR.MATH is a strong choice.
- 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 the direction used for negative values.
- Function choice: Use FLOOR.MATH when the rule is "downward to the previous valid step."
Tell your friends about this post