How to use the Excel FLOOR function
In this simple guide, we will run through how to use the Excel FLOOR function. We will cover:
- What does the FLOOR function mean?
- The formula
- FLOOR vs FLOOR.MATH
What does the FLOOR function mean?
Put simply, the Excel FLOOR function takes a specified number, and rounds it down to the nearest specified multiple.
That’s it!
One quick caveat however, is that if the number we have chosen to round down is already a number that is a multiple of what we have chosen, then it simply stays as is, rather than rounding down to the next lowest.
Finally, it is also worth noting that the overall functionality and output is very similar to the MROUND formula, but the output of floor will always round down. MROUND for example finds the nearest value to round to whether it is up or down. Both have their uses, but if you only ever want to round down then FLOOR is the option to go with.
As mentioned, this whole function is super simple and only really requires the two specified values.
Let’s take a quick look at the formula itself.
The FLOOR formula
The formula for FLOOR in excel is also very simple. It only requires the word FLOOR, followed by two values inside of the parentheses – the starting value, and the multiple to round down to:
=FLOOR(value, multiple)
For example, let’s look at the number 10, with a significance of 3. If we use these values then the output we are provided with is 9:
Another example worth noting is what happens when we enter a value that is too low – for example 2 and 3. Because we are always rounding down rather than up, this simply outputs a zero:
Here are a few more examples with different values and multiples:
FLOOR vs FLOOR.MATH
A newer feature in Excel is the FLOOR.MATH formula, which works in almost the exact same way as FLOOR with more basic inputs, but it has a couple of key differences.
First, is that the it defaults to a multiple of one, leaving this field as entirely optional. With the original FLOOR formula, if you leave the second value blank you will receive an error.
Second, is that it provides additional support for customising the output of negative numbers through the mode value. This is a third value you can enter, which defaults to zero, but allows us to specify whether or not we want a negative number to round towards or away from zero. Leaving blank to default zero means it will continue going down and away from zero. However if we change the value to 1, it rounds up towards zero. For example:
Mode at 0:
Mode at 1:
With newer versions of Excel, generally it is recommended to use the FLOOR.MATH function as not only does it contain all of the features of the standard FLOOR function, but the original FLOOR formula has purely been left in as a legacy feature. While there are no plans that we are aware of to remove this old formula, there is really no reason to continue to use it and there is nothing to say that eventually it won’t be removed, leaving users in a position to potentially need to re-work some formulas in historical documents.
Finally, here are some broader examples of using the Excel FLOOR.MATH function, some with and without the multiple and mode being used:
This sums up our guide on the Excel FLOOR and FLOOR.MATH functions. As we have shown, it is quite a simple but powerful feature that quickly rounds down numbers for us.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.
