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

Excel CEILING Function

Related Functions:
FLOOR Function
MROUND Function
Excel Rounding Functions

The Excel Ceiling function is one of fifteen rounding functions in Excel.

A full list, along with comparison tables, is provided on the Excel Rounding Functions page.

Function Description

The Excel Ceiling function rounds a supplied number 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 number that is to be rounded.
significance -

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

(This 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 number argument. If they have different arithmetic signs, the Ceiling function returns an error.

However, in later versions of Excel, 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 (i.e. rounds the supplied number towards zero).

Ceiling Function Examples

In column B of the following spreadsheet, the Excel Ceiling function is used to round the positive and negative numbers 22.25 and -22.25 to different significance values.

 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 away from zero.


Examples with Positive and Negative Arguments

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

The results shown are those that would be obtained in current versions of Excel (Excel 2010 and later).

 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  - returns an error in Excel 2007 & earlier
5 -22.25 -23

This example shows how:


For further information on the Excel Ceiling Function, see 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 or
Excel 2013:		 - if the supplied number is positive and the supplied significance is negative.
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 any of the supplied arguments are non-numeric.
Return to the Excel Math Functions Page
Return to the List of All Built-In Excel Functions