The FLOOR.MATH function will round any number down to the nearest integer and if desired, a specific multiple.
Contents:
Syntax
= FLOOR.MATH(number, [significance], [mode])number = negative or positive number to be rounded down
[significance] = optional, multiple to which the number will be rounded down to
[mode] = optional, changes the direction in which negative numbers are rounded
Explanation
The FLOOR.MATH function is part of the "Math and Trigonometry" group of functions within Excel.
This function combines two other functions, the INT function and the MROUND function. FLOOR.MATH will round any number, positive or negative, down to the nearest integer or desired multiple.
Using the optional significance argument will change the formula to round down to the specified multiple. A multiple being any number that can be divided by another number without a remainder left over.
For example, 17 rounded to the nearest multiple of 10, would be rounded downwards to 10. While an 11 rounded to the nearest multiple of 5, would be rounded downwards to 5.
If the number is already an exact multiple, no rounding will be performed.
The mode argument can only be used when rounding negative numbers and changes the direction of the round. The default behavior is to round away from zero.
There are two inputs that can be used for the Mode argument.
Mode = 0 or "FALSE" - (default behavior) rounds away from zero
Mode = 1 or "TRUE" - rounds up towards zero
Notes:
- If any input is non-numerical, a #VALUE! error will be returned
- The number and the multiple must have the same sign
- A blank cell will return a 0
Different Methods of Rounding in Excel
There are a lot of different ways to round numbers, and it all depends on the end goal you're trying to reach. Here are a few different rounding functions and when you would want to use them:
ROUND - normal rounding, rounds a specified place
MROUND - rounds to the nearest specified multiple
ROUNDUP - rounds up away from 0, to the nearest specified place
CEILING.MATH - rounds up to the nearest integer or significant multiple
ROUNDDOWN -rounds down away from 0, to the nearest specified place
FLOOR.MATH - rounds down to the nearest integer or significant multiple
INT - rounds down to the nearest integer, returns an integer
TRUNC - truncates all decimal places to specified place
Examples
1. How to Round a Number Down to The Nearest Integer
To round a number to the nearest integer, we can use the FLOOR.MATH function with any input number (either a cell reference or hard coded value).
Simply drop in any number into the number argument position of the formula and the formula will automatically round any number with a decimal or fractional portion to the nearest integer.
= FLOOR.MATH(number)
2. How to Round a Number Down to The Nearest Integer and Specific Multiple
The FLOOR.MATH function can also be used to round numbers down to the nearest integer and specific multiple.
By adding any number to the Significance argument, the formula will round any input number down to the nearest specified multiple or number of significance.
= FLOOR.MATH(number, multiple3. How to Round Negative Numbers to Nearest Integer, Towards or Away from Zero
The FLOOR.MATH function also has the built-in ability to change whether negative numbers are rounded away from zero or towards zero.
This gives more control over the exact rounding behavior, allowing better precision over most other rounding functions.
In the first two examples, 0 is being used in the Mode argument spot to round the input numbers away from zero. While in the last two examples, 1 is being used to round towards zero.
Formula to round away from zero:
= FLOOR.MATH(number,,0)Formula to round towards zero:
= FLOOR.MATH(number,,1)