Exceljet

Quick, clean, and to the point

Excel CEILING.MATH Function

Excel CEILING.MATH function
Summary 

The Excel CEILING.MATH function rounds a number up to a specified multiple. Unlike the CEILING function, CEILING.MATH defaults to a multiple of 1, and provides explicit control over rounding direction for negative numbers.

Purpose 
Round a number up to nearest multiple
Return value 
A rounded number.
Syntax 
=CEILING.MATH (number, [significance], [mode])
Arguments 
  • number - The number that should be rounded.
  • significance - [optional] Multiple to use when rounding. Default is 1.
  • mode - [optional] For rounding negative numbers toward or away from zero. Default is 0.
Usage notes 

The Excel CEILING.MATH function will round a number up to the nearest integer or given multiple of significance. By default, CEILING.MATH rounds to the nearest integer, using a significance of 1. Positive numbers with decimal portions are rounded up to the nearest integer (e.g. 6.3 is rounded up to 7) and negative numbers with decimal portions are rounded toward zero (e.g. -6.3 is rounded up to -6). 

=CEILING.MATH(6.3) // returns 7
=CEILING.MATH(-6.3) // returns -6

Rounding negative numbers

Control for rounding negative numbers toward zero or away from zero is provided via the (optional) mode argument. Mode defaults to zero. When mode is zero, or omitted, CEILING.MATH rounds negative numbers toward zero. When mode is any non-zero value, CEILING.MATH rounds negative numbers away from zero. Mode has no effect on positive numbers.

=CEILING.MATH(-4.1) // returns -4
=CEILING.MATH(-4.1,1) // returns -4
=CEILING.MATH(-4.1,1,1) // returns -5
=CEILING.MATH(-4.1,1,-1) // returns -5
=CEILING.MATH(-4.1,1,TRUE) // returns -5

CEILING.MATH vs CEILING

The CEILING.MATH function differs from the CEILING function in these ways:

  1. CEILING.MATH provides a default multiple of 1 for positive numbers and -1 for negative numbers
  2. CEILING.MATH provides explicit control for rounding negative numbers (toward zero, away from zero)
  3. CEILING.MATH appears to use the absolute value of the significance argument. Changing the sign of significance has no effect on the result.

Notes

  • To round to the nearest multiple (either up or down) see the MROUND function.
  • CEILING.MATH works like CEILING, but provides control for rounding direction for negative values. 
  • The mode argument has no effect on positive numbers.
  • If number is an exact multiple of significance, no rounding occurs.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.