Excel CEILING.MATH function

Summary

The Excel CEILING.MATH function is designed to round a number up to a specified multiple. This function extends the capabilities of the traditional CEILING function, offering a default rounding to the nearest integer and explicit control for rounding direction, especially for negative numbers.
Syntax
				
					=CEILING.MATH(number, [significance], [mode])
				
			
  • number: The numeric value to round up
  • [significance]: [Optional] The multiple to round to. Default is 1
  • [mode]: [Optional] Determines how negative numbers are rounded: 0 (or FALSE) rounds toward zero, 1 (or TRUE) rounds away from zero. Default is 0
Return value
A rounded number, adjusted to the nearest specified multiple.

How to use

CEILING.MATH rounds a number to the nearest specified multiple. If no multiple is specified, it defaults to 1. The mode argument provides additional control for rounding negative numbers.

Examples

Simple CEILING.MATH
Rounding to Nearest Integer: Rounds a number to the nearest integer:
				
					=CEILING.MATH(1.25)
				
			
This formula rounds 1.25 up to the nearest integer, which is 2.
CEILING.MATH with Specified Multiple
Rounding to a Specified Multiple: Rounding a number to a specified multiple:
				
					=CEILING.MATH(4.1, 0.5)
				
			
Rounds 4.1 up to the nearest multiple of 0.5, resulting in 4.5.
CEILING.MATH with Negative Numbers
Controlling Rounding Direction for Negative Numbers: Rounding negative numbers with different modes:
				
					=CEILING.MATH(-4.1) // returns -4
=CEILING.MATH(-4.1, 1, 1) // returns -5
				
			
The first formula rounds -4.1 toward zero, resulting in -4. The second formula rounds away from zero, resulting in -5.
CEILING.MATH with Fractional Significance
Rounding Up to Nearest Fractional Value: To round a number up to the nearest quarter (.25):
				
					=CEILING(10.1, 0.25)
				
			
This rounds 10.1 up to the nearest quarter, resulting in 10.25.
CEILING.MATH vs. CEILING

Comparing CEILING.MATH with CEILING Function: Highlighting differences in rounding behavior:

				
					=CEILING.MATH(-4.1, 1) // returns -4
=CEILING(-4.1, 1) // returns -5
				
			
CEILING.MATH provides more control over rounding negative numbers compared to the traditional CEILING function.

Additional Notes

  • CEILING.MATH is particularly useful when rounding to the nearest multiple, especially with the default significance of 1.
  • The mode argument in CEILING.MATH is a distinctive feature that offers precise control over the rounding of negative numbers.
  • If the number is already an exact multiple of significance, no rounding occurs.

Related Functions

Excel ROUNDUP function

The Excel ROUNDUP function rounds a number up, away from zero, to a specified number of digits, ideal for precautionary measures in calculations.

Excel FLOOR.MATH function

The Excel FLOOR.MATH function rounds numbers down to the nearest integer or multiple, flexible for financial and statistical analysis.

Excel ROUNDDOWN function

The Excel ROUNDDOWN function rounds a number down, away from zero, to a specified number of digits, useful for conservative estimations.

Excel CEILING function

The Excel CEILING function rounds numbers up to the nearest multiple of significance, ideal for pricing and budgeting calculations.

Excel FLOOR function

The Excel FLOOR function rounds numbers down to the nearest multiple of significance, useful for inventory and budget control.

Excel ROUND function

The Excel ROUND function rounds a number to a specified number of digits, crucial for accurate financial and scientific calculations.

Content Navigation