FLOOR.MATH function

The FLOOR.MATH function in Excel is used to round a number down to the nearest multiple of a specified significance, with more control and flexibility than the older FLOOR function. It allows you to specify how to round both positive and negative numbers, offering more precise rounding options.

Syntax

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

Parameters

• number: The number you want to round down.
• significance (optional): The multiple to which you want to round the number. If omitted, Excel defaults to 1.
• mode (optional): A value that determines how to round negative numbers. Use 0 to round down to the nearest multiple of significance (default), or use 1 to round away from zero for negative numbers.

Return Value

The function returns a number that is rounded down to the nearest multiple of the specified significance. It can round down to either a positive or negative multiple based on the mode.

Example 1: Round Down to the Nearest Multiple of 5

To round 12 down to the nearest multiple of 5:

=FLOOR.MATH(12, 5)

Result: 10
Explanation: The nearest multiple of 5 less than or equal to 12 is 10.

Example 2: Round Down to the Nearest Multiple of 0.5

To round 7.3 down to the nearest multiple of 0.5:

=FLOOR.MATH(7.3, 0.5)

Result: 7.0
Explanation: The nearest multiple of 0.5 less than or equal to 7.3 is 7.0.

Example 3: Round Down a Negative Number Using Mode

To round -7.5 down to the nearest multiple of 2 using the default mode:

=FLOOR.MATH(-7.5, 2)

Result: -8.0
Explanation: The nearest multiple of 2 less than or equal to -7.5 is -8.0 (since we round down towards negative infinity).

Example 4: Round Negative Number Away from Zero

To round -7.5 away from zero (i.e., round up towards -6):

=FLOOR.MATH(-7.5, 2, 1)

Result: -6.0
Explanation: Using mode 1, the function rounds -7.5 away from zero to the next multiple of 2, which is -6.0.

Key Differences Between FLOOR and FLOOR.MATH

• The FLOOR.MATH function provides an optional mode argument, allowing you to control how negative numbers are rounded (either rounding down to the nearest multiple or rounding away from zero).
• The significance parameter is required in FLOOR, but in FLOOR.MATH, if you omit it, Excel defaults to rounding to the nearest 1.

Important Notes

• The FLOOR.MATH function was introduced in Excel 2013 and later versions. It offers more versatility compared to the older FLOOR function.
• If you use mode = 0 (or omit it), negative numbers will always round down (toward negative infinity).
• The significance should always be a positive number; otherwise, the function will return a #NUM! error.

Use Case

The FLOOR.MATH function is useful for scenarios where precise control over rounding is necessary, especially when working with both positive and negative numbers in financial, engineering, or statistical calculations.