CEILING.MATH

Using the Excel CEILING.MATH Function

Summary

The Excel CEILING.MATH function is similar to the CEILING function in that it is used to round a given number up to the nearest integer or a user specified significance level. CEILING.MATH defaults to a significance of 1 and rounds to the nearest integer. The significance level can include decimal or whole numbers. CEILING.MATH is useful for users looking for increased functionality when handling negative numbers. This is accomplished via the mode argument (optional).

Syntax

=CEILING.MATH (number, [significance], [mode])

Syntax Breakdown

number
The number argument is required in the CEILING.MATH function. This is the value that you wish to round up. Numbers must be above -2.229E-308 and below 9.99E+307.

[significance]
Significance is optional and is the user specified multiple that number should be rounded up to. If significance isn't defined, the default values of +1 for positive numbers and -1 for negative numbers will be used.

[mode]
Mode is optional and is used to indicate if negative numbers should be rounded towards or away from zero. Mode has no impact on positive numbers.

When mode is set to zero or omitted the function will round negative numbers towards zero. This is the default setting. When mode is set to a non-zero number the function rounds negative numbers away from zero.

Excel CEILING.MATH Mode Example


Usage Notes

The CEILING.MATH function is useful when you are required to round a number up to the nearest integer. When dealing with decimals, positive numbers will round up to the nearest integer and negative numbers will also round up towards zero. For example, 5.1 would round up to 6 and -5.9 would round to -5.

CEILING.MATH Errors
If the CEILING.MATH function receives either argument (number or significance) as a non-numerical value, the #VALUE! error is returned.

Excel CEILING.MATH #VALUE Error

Compared to CEILING
CEILING.MATH has a few advantages over the CEILING function.

  • CEILING.MATH allows the user to control the rounding for negative numbers. This functionality is not available in CEILING.
  • CEILING.MATH's default value for positive numbers is 1 and for negative numbers it is -1.