Excel FLOOR Function
Summary
The Excel FLOOR function rounds a given number down to the nearest specified multiple. FLOOR works like the MROUND function, but FLOOR always rounds down.
Purpose
Round a number down to the nearest specified multiple
Return value
A rounded number.
Syntax
=FLOOR (number, multiple)
Arguments
- number - The number that should be rounded.
- multiple - The multiple to use when rounding.
Usage notes
The Excel FLOOR function rounds a number down to a given multiple. For example, the following could be used to round a price in A1 down to the nearest 5 dollars.
=FLOOR(A1,5)
FLOOR can be a can be a useful function to set pricing after currency conversion, discounts, etc. For example, the formula below will round a value in A1 down to the nearest 1 dollar, then subtract 1 cent, to get a price like $2.99, $5.99, $49.99, etc.
=CEILING(A1,1) - 0.01
If you need more control over how FLOOR rounds negative numbers, see the FLOOR.MATH function.
FLOOR vs FLOOR.MATH
The FLOOR function differs from the FLOOR.MATH function in these ways:
- FLOOR.MATH provides a default significance of 1, rounding to nearest integer.
- FLOOR.MATH provides support for rounding negative numbers (toward zero, away from zero).
- FLOOR.MATH appears to use the absolute value of the significance argument. Changing the sign of significance has no effect on the result.
Notes
- FLOOR works like the MROUND function, but FLOOR always rounds down.
- With a positive number, the value is rounded down toward zero.
- With a negative number, the value is rounded down away from zero.
- The FLOOR function can be a good way to can be a good way to set pricing after currency conversion, discounts, etc.
FLOOR formula examples
If you need to group times into buckets (i.e. group by 2 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR.
In the example shown, we have a number of transactions, each with a time...
To calculate required currency denominations, given a specific amount, you can build a currency calculation table as shown in the example. This solution uses the INT and SUMPRODUCT functions.
In the example show, the...
If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits.
How this formula works
In the example, cell C6 contains this formula:
=MROUND(B6,5)
The...
If you need to round a number down to the nearest specified multiple (i.e. round a number down to the nearest dollar, down to the nearest $.25, down to the nearest multiple of 5, etc.) you can use the FLOOR function....
Related functions
