The `FLOOR.MATH` function in Excel is used to round a number down to the nearest integer or specified multiple. It provides more control compared to the basic `FLOOR` function, allowing for different rounding modes.
Here’s how to use the `FLOOR.MATH` function:
Syntax
FLOOR.MATH(number, [significance], [mode])
Parameters
- number: (required) The number you want to round down.
- significance: (optional) The multiple to which you want to round the number. If omitted, it defaults to 1.
- mode: (optional) Determines the direction of rounding for negative numbers.
Usage
=FLOOR.MATH(3.7)
- Basic Usage:
- To round a number down to the nearest integer:
Result: 3
=FLOOR.MATH(7.5, 2)
- Using Significance:
- To round a number down to the nearest specified multiple:
Result: 6 (rounds down to the nearest multiple of 2)
=FLOOR.MATH(-7.5, 2, 1)
- Rounding Negative Numbers:
- By default, for negative numbers, `FLOOR.MATH` rounds away from zero. To change this, use the `mode` parameter.
- Round towards zero for negative numbers:
Result: -6 (rounds towards zero)
Example Scenarios
=FLOOR.MATH(15.5, 2)
- Rounding Down to Even Mulitples: If you need to round down a measurement to the nearest even number:
Result: 14
=FLOOR.MATH(123.45, 1)
- Financial Calculations: When dealing with currency, you might want to round down to the nearest dollar or cent:
Result: 123
=FLOOR.MATH(47, 5)
- Custom Roundings based on Business Rules: E.g., rounding down to the nearest 5 for some inventory purposes:
Result: 45
Notes
- If `number` is positive and `significance` is negative, `FLOOR.MATH` will return an error. The same vice versa.
- The `significance` should never be 0; otherwise, it will result in an error.
By understanding these aspects of `FLOOR.MATH`, you can effectively round numbers in Excel to meet specific criteria.
Unlock Your Potential
Excel
Access
Power BI
