Related Functions:

The CEILING function returns number rounded up, away from zero, to the nearest multiple of significance.




Argument Description
number The value you want to round
significance The multiple to which you want to round

Note: In Excel 2003 & 2007, the significance argument must have the same arithmetic sign, positive or negative, as the number argument. 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.


  A B C
1 Formula Result Notes
2 =CEILING(2.5,1) 3 Rounds 2.5 up to nearest multiple of 1
3 =CEILING(-2.5,-2) -4 Rounds -2.5 up to nearest multiple of -2
4 =CEILING(-2.5,2) -2 Rounds -2.5 up to nearest multiple of 2
5 =CEILING(0.234,0.01) 0.24 Rounds 0.234 up to the nearest multiple of 0.01

Usage note: If you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either of the supplied arguments is non-numeric
#NUM! Occurs if either:

  In Excel 2010 or
Excel 2013:
if the supplied number is positive and the supplied significance is negative
  In Excel 2007 or
if the supplied significance value has a different arithmetic sign to the supplied number argument
#DIV/0! Occurs if the supplied significance argument = 0