The `CEILING.MATH` function in Excel is used to round a number up to the nearest integer or to the nearest multiple of a specified significance. It is a versatile function that provides greater control over the rounding behavior compared to other rounding functions. Here’s how to use it:
Syntax
CEILING.MATH(number, [significance], [mode])
Parameters
- number (required): The number you want to round up.
- significance (optional): The multiple to which you want to round. If omitted, the default significance is 1.
- mode (optional): Determines the direction of rounding for negative numbers. If `mode` is 0 or omitted, negative numbers are rounded away from zero. If `mode` is non-zero, negative numbers are rounded towards zero.
Example Usage
=CEILING.MATH(2.3)
- Basic Rounding Up
This will return `3`, rounding 2.3 up to the nearest integer.
=CEILING.MATH(2.3, 0.5)
- Rounding Up to a Specified Multiple
This will return `2.5`, rounding 2.3 up to the nearest multiple of 0.5.
=CEILING.MATH(-2.3)
- Rounding Negative Numbers with Different Modes
- With mode omitted or 0:
This will return `-3`, rounding -2.3 away from zero to the nearest integer.
=CEILING.MATH(-2.3,,1)
This will return `-2`, rounding -2.3 towards zero.
Notes
- `CEILING.MATH` is available in Excel 2013 and later versions.
- Unlike the `CEILING` function, `CEILING.MATH` will round negative values based on the mode parameter, allowing for more flexibility in mathematical calculations.
- Always ensure that the `significance` parameter is compatible with the data type of the `number` to avoid errors or unexpected results.
This function is particularly useful in financial and engineering calculations where precise control over rounding is essential.
Unlock Your Potential
Excel
Access
Power BI
