ExcelFunctions.net

Excel CEILING Function

Related Functions:
Home » Excel-Built-In-Functions » Excel-Math-Functions » Excel-Ceiling-Function

Basic Description

The Excel Ceiling function rounds a supplied number up or down, away from zero, to the nearest multiple of a given number.

The syntax of the function is:

CEILING( number, significance )

Where the arguments are as follows:

number - The initial number
significance -

The multiple of significance that the supplied number should be rounded to.

(should generally have the same arithmetic sign (positive or negitive) as the supplied number argument)

Positive and Negative Arguments

Note: In Excel 2003 & 2007, the significance argument must have the same arithmetic sign (positive or negative) as the initial number argument. However, in Excel 2010, the Ceiling function has been improved, so that it can now handle a negative number argument and a positive significance argument. In this case, the Ceiling function reverses the direction of the rounding (ie. rounds the supplied number towards zero).

Ceiling Function Examples

The following spreadsheet shows the Excel Ceiling function used with several different significance values, on the positive and negative numbers 22.25 and -22.25.

The format of the functions is shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

 Formulas:
  A B
1 number ceiling
2 22.25 =CEILING( A2, 0.1 )
3 22.25 =CEILING( A3, 0.5 )
4 22.25 =CEILING( A4, 1 )
5 22.25 =CEILING( A5, 10 )
6 22.25 =CEILING( A6, 20 )
7 -22.25 =CEILING( A7, -0.1 )
8 -22.25 =CEILING( A8, -1 )
9 -22.25 =CEILING( A9, -5 )
 Results:
  A B
1 number ceiling
2 22.25 22.3
3 22.25 22.5
4 22.25 23
5 22.25 30
6 22.25 40
7 -22.25 -22.3
8 -22.25 -23
9 -22.25 -25

The above examples show how, when the number and the significance arguments have the same arithmetic sign, the Ceiling function rounds the supplied number away from zero.


Examples with Positive and Negative Arguments

Different combinations of positive and negative arguments, are shown in the Excel 2010 spreadsheet below.

 Formulas:
  A B
1 number ceiling
2 22.25 =CEILING( A2, 1 )
3 22.25 =CEILING( A3, -1 )
4 -22.25 =CEILING( A4, 1 )
5 -22.25 =CEILING( A5, -1 )
 Results:
  A B
1 number ceiling
2 22.25 23
3 22.25 #NUM!
4 -22.25 -22
5 -22.25 23  - returns an error in Excel 2007 & earlier

This example shows how:

  • a positive number argument and a negative significance argument results in an error
  • a negative number argument and a positive significance argument results in an error in Excel 2007 and earlier, but in Excel 2010, is handled by reversing the direction of the rounding (i.e. rounding the negative number towards zero)

Further information on the Excel Ceiling Function is provided on the Microsoft Office website.


Ceiling Function Error

If you get an error from the Excel Ceiling function, this is likely to be one of the following :

Common Errors
#NUM! - Occurs :
In Excel 2010 : if the supplied number is positive and the supplied significance is negative
or
In Excel 2007 or earlier : if the supplied significance value has a different arithmetic sign to the supplied number argument
#DIV/0! - Occurs if the supplied significance argument = 0
#VALUE! - Occurs if either of the supplied arguments is non-numeric