In Excel, the CEILING.MATH function rounds a number up to the nearest specified multiple. Introduced in Excel 2013, it is a more flexible replacement for the older CEILING function, providing greater control over how negative numbers are rounded.
Syntax and arguments
The CEILING.MATH function uses the syntax CEILING.MATH(number, [significance], [mode]).
- number (Required): The value to be rounded up.
- [significance] (Optional): The multiple for rounding. Defaults to 1 if omitted. The function ignores the sign of significance.
- [mode] (Optional): Controls rounding for negative numbers. 0 or omitted rounds toward zero, while any non-zero value rounds away from zero.
How CEILING.MATH works
Positive numbers
For positive numbers, CEILING.MATH always rounds up, away from zero. The mode argument does not affect positive values. Examples include rounding 24.3 up to 25 with a significance of 5, or 6.7 up to 7 with a default significance of 1.
Negative numbers
Negative numbers are rounded based on the mode argument.
- Rounding toward zero (mode = 0 or omitted): Rounds to the nearest multiple of
significancecloser to zero. For example,=CEILING.MATH(-8.1, 2)returns -8. - Rounding away from zero (mode = 1 or non-zero): Rounds to the nearest multiple of
significancefurther from zero. For example,=CEILING.MATH(-5.5, 2, 1)returns -6.
Practical applications
CEILING.MATH is useful for tasks requiring precise upward rounding. Examples include:
- Financial calculations: Rounding currency values up to specific increments, like rounding $4.42 up to $4.45 with a significance of $0.05.
- Inventory and order quantities: Rounding up material amounts to match batch sizes, such as rounding 550 units up to 600 units if materials come in batches of 100.
- Time calculations: Rounding timestamps up to the next interval, like rounding time in cell A1 up to the nearest 15 minutes using
=CEILING.MATH(A1, "0:15").
CEILING.MATH vs. CEILING
CEILING.MATH is the preferred function over the older CEILING due to its flexibility, particularly with negative numbers. Key differences include:
| Feature | CEILING.MATH | CEILING (Compatibility) |
|---|---|---|
| Significance default | Defaults to 1. | No default; returns an error if omitted. |
| Negative number rounding | mode argument controls direction. |
Less predictable behavior; often rounds away from zero. |
| Significance sign | Ignores the sign of significance. |
Sign of significance must match number to avoid #NUM! error. |
Summary of related rounding functions
Other Excel functions provide different rounding capabilities:
- FLOOR.MATH: Rounds a number down.
- MROUND: Rounds up or down to the nearest multiple.
- ROUNDUP: Rounds up to a specified number of decimal places.
- ROUNDDOWN: Rounds down to a specified number of decimal places.
- INT: Rounds down to the nearest integer.
- TRUNC: Removes the fractional part of a number.