Math and trigonometry
CEILING.MATH Function
Rounds a number up to the nearest integer or multiple of
significance. Optionally rounds negative numbers away from zero.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Rounds a number up to the nearest integer or multiple of
significance. Optionally rounds negative numbers away from zero.
Syntax
CEILING.MATH(number, significance, mode)
 number Required.
Must be less than 9.99E+307 and greater than 2.229E308.
 significance Optional.
Default=+1
for positive numbers and 1 for negative
numbers. The multiple to which number is to be
rounded. A significance of 1 rounds number
to an integer.
 mode Optional.
Default = 0. A mode
value of zero rounds negative numbers up. Any other mode
value rounds negative numbers away from zero. This argument
does not affect positive numbers.
Applies To
Excel 2013 and above
A Note About
"Rounding"
In Excel, numbers can "round" in two ways.
First, they can round up or down. With this type of rounding,
numbers
get larger when they are rounded up, and smaller when they are
rounded down.
Second, numbers can round towards zero or away from zero.
When positive numbers are rounded towards zero, they get
smaller; when negative numbers are rounded towards zero, they get larger.
Although Microsoft uses "round up" and "round down" to
describe both types of rounding, we use "round up" or "round
down" to describe the first type of rounding. And we use "round
towards zero" or "round away from zero" to describe the second
type.
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
No Significance value, or a Significance value of 1, rounds the number up to the nearest
integer. For positive numbers, rounds numbers up for any Mode value. For negative
numbers, no Mode value, or a value of zero, rounds numbers up. A nonzero Mode value
rounds negative numbers away from zero.
To illustrate how CEILING.MATH works with a nonzero mode, examples 10b and 11b
show how INT can produce the same results as CEILING.MATH in 10a and 11a,
respectivey, by rounding numbers away from zero in both instances.
INT and CEILING.MATH can produce the same results even when the significance
value is not an integer.
The CEILING function returns an error value if the number is positive and
Significance is negative. But CEILING.MATH returns a value.
Other ExcelUser
Information
Other Help
Related Functions
In total,
Excel 2013 offers 15 functions that round data in
various ways. Here's a
summary of Excel's rounding functions.
