The `FLOOR` function in Excel is used to round a number down toward zero, to the nearest multiple of a specified significance. Here’s how you can use it:
Syntax
FLOOR(number, significance)
- number: The numerical value that you want to round down.
- significance: The multiple to which you want to round down the number.
Example Usage
Suppose you have the number 23.56 and you want to round it down to the nearest multiple of 0.1.
=FLOOR(23.56, 0.1)
This formula will return `23.5`.
Considerations
- The `number` and `significance` must have the same signs. If one is positive and the other is negative, the `FLOOR` function will return an error.
- If the `significance` is set to zero, Excel will return a `#NUM!` error, as it can’t round to a multiple of zero.
- For negative numbers, the `FLOOR` function rounds away from zero.
Example Scenarios
=FLOOR(9.99, 1)
- Rounding Prices: If you want to round down prices to the nearest dollar:
This will return `9`.
=FLOOR(A1, "0:15")
- Rounding Time: For rounding time to the nearest 15 minutes:
Assuming `A1` contains a time value, this will round it down to the nearest 15-minute mark.
Remember, there is an updated version of this function called `FLOOR.MATH`, which provides more options for rounding, including the ability to specify a mode for rounding negative numbers.
Unlock Your Potential
Excel
Access
Power BI
