Excel FLOOR.MATH Function: Syntax & Examples

The Excel FLOOR.MATH function rounds a number down to the nearest multiple of a specified significance, with the option to round toward or away from zero. You enter a number, an optional significance (default is 1), and an optional mode; the function returns the rounded-down value. This article explains the syntax, how it differs from FLOOR and FLOOR.PRECISE, and gives practical examples.

Formula

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

• x (required) – the numeric value you want to round down.
• significance (optional) – the multiple to which you want to round. Defaults to 1 if omitted.
• mode (optional) – for negative numbers, controls the direction of rounding. If 0 or omitted, negative numbers are rounded away from zero (i.e., toward negative infinity). If any other number, negative numbers are rounded toward zero.

The result is the largest multiple of significance that is less than or equal to x (when mode is 0), or the largest multiple that is greater than or equal to x when rounding toward zero for negatives.

Worked Example

Using the default values from the calculator: x = 4.7, significance = 1, mode = 0.

1. Divide 4.7 by 1: 4.7 / 1 = 4.7
2. Apply FLOOR to the quotient: FLOOR(4.7) = 4
3. Multiply by significance: 4 × 1 = 4

The result is 4. This matches the calculator output.

When to Use FLOOR.MATH

FLOOR.MATH is useful in pricing, inventory, time calculations, and any scenario where you need to round down to a standard unit. For example:

• Round a price down to the nearest dollar: =FLOOR.MATH(19.99, 1) → 19
• Round a time (in hours) down to the nearest quarter hour: =FLOOR.MATH(3.7, 0.25) → 3.5
• Round a negative value down to the nearest multiple of 10, away from zero: =FLOOR.MATH(-8, 10) → -10
• Round a negative value toward zero: =FLOOR.MATH(-8, 10, 1) → 0

Common Mistakes and Misconceptions

One frequent error is confusing FLOOR.MATH with FLOOR. The older FLOOR function requires both arguments and has different behavior for negative numbers. FLOOR.MATH is more flexible because significance defaults to 1 and the mode argument gives control over negative rounding.

Another mistake is forgetting that the significance must be positive. If you enter a negative significance, FLOOR.MATH returns a #NUM! error. Also, if x is positive and significance is positive, the result is always less than or equal to x.

Rounding down is not the same as truncating. For positive numbers, it is the same, but for negative numbers, truncation removes the decimal part (e.g., -4.7 → -4) while FLOOR.MATH with default mode rounds to -5.

Comparison with Related Functions

Excel offers several rounding functions. Here’s how they differ:

• FLOOR – older function, requires both arguments, rounds away from zero for negatives.
• FLOOR.MATH – modern default, optional significance and mode, rounds toward negative infinity by default.
• FLOOR.PRECISE – rounds to the nearest multiple, always toward zero for negatives (no mode option).
• CEILING.MATH – the opposite of FLOOR.MATH, rounds up.
• INT – rounds down to the nearest integer (equivalent to FLOOR.MATH with significance 1).
• ROUNDDOWN – truncates toward zero, not the same as floor for negatives.

For most new work, FLOOR.MATH is recommended over FLOOR because it is more intuitive and handles edge cases better.

Limitations and Edge Cases

FLOOR.MATH works with any real numbers, but there are a few limitations:

• If significance is 0, the function returns 0.
• If x is exactly a multiple of significance, it returns x unchanged.
• If x is positive and significance is negative, it returns #NUM!.
• Very large numbers may lose precision due to floating-point limits.

For time calculations, remember that Excel stores times as fractions of a day, so to round to the nearest hour, use significance = 1/24.

Frequently Asked Questions

What is the difference between FLOOR and FLOOR.MATH?

FLOOR requires both the number and significance arguments, and it always rounds negative numbers away from zero. FLOOR.MATH defaults significance to 1 and allows you to control the direction for negatives with the mode argument, making it more flexible and easier to use.

How do I round down to the nearest 5 or 10 in Excel?

Use FLOOR.MATH with the significance set to 5 or 10. For example, =FLOOR.MATH(23, 5) returns 20, and =FLOOR.MATH(23, 10) returns 20. For negative numbers, the default rounds away from zero, so =FLOOR.MATH(-23, 10) returns -30.

Why does FLOOR.MATH return #NUM!?

This error occurs when the significance argument is negative. Significance must be a positive number. If you need to round to a negative multiple, consider using a positive significance and then negating the result manually.

Can FLOOR.MATH round to a decimal place?

Yes, by setting significance to a decimal value. For example, =FLOOR.MATH(4.567, 0.01) rounds down to 4.56. For rounding to a specific number of decimal places, you can also use ROUNDDOWN, but FLOOR.MATH gives you more control over the multiple.

Is FLOOR.MATH available in all Excel versions?

FLOOR.MATH was introduced in Excel 2013 and is available in Excel 2016, 2019, 365, and later versions. It is also available in Google Sheets. If you are using an older version, use FLOOR or FLOOR.PRECISE instead.

How does FLOOR.MATH handle negative numbers differently from CEILING.MATH?

FLOOR.MATH rounds negative numbers away from zero by default (e.g., -4.7 → -5), while CEILING.MATH rounds negative numbers toward zero (e.g., -4.7 → -4). The mode argument in both functions can reverse this behavior.

Related

• Fix iPhone Clock: Change Date, Time, or Time Zone
• Epic Games Launcher Stuck on Loading Your Order? Fix
• Excel Count Days Between Dates Excluding Weekends

in x | Number | - | 4.7 | 0
in s | Significance | - | 1 | 0
in m | Mode | - | 0 | 0
out result | Rounded down | - | floor(x/s)*s | 2