For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

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, 2005-2014
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.229E-308.
  • 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.

Examples for Excel's CEILING.MATH function

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 non-zero Mode value rounds negative numbers away from zero.

Examples for Excel's CEILING.MATH function

To illustrate how CEILING.MATH works with a non-zero 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.

Examples for Excel's CEILING.MATH function

INT and CEILING.MATH can produce the same results even when the significance value is not an integer.

Examples for Excel's CEILING.MATH function

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.

 

Charley's SwipeFile charts


Free Excel Dashboards