Exceljet

Quick, clean, and to the point

Excel CEILING Function

Excel CEILING function
Summary 

The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function, but CEILING always rounds up.

Purpose 
Round a number up to nearest multiple
Return value 
A rounded number.
Syntax 
=CEILING (number, multiple)
Arguments 
  • number - The number that should be rounded.
  • multiple - The multiple to use when rounding.
Usage notes 

The Excel CEILING function rounds a number up to a given multiple.

Example #1 - round up to nearest 5

To round a number in A1 up to the nearest multiple of 5, you can use a formula like this:

=CEILING(A1,5)

Example #2 - round pricing up to end with .99

CEILING can be a can be useful to set pricing after currency conversion or discounts are applied. For example, the formula below will round a value in A1 to the nearest 1 dollar, then subtract 1 cent, and return a price like $2.99, $5.99, $49.99, etc.

=CEILING(A1,1) - 0.01

Example #3 - round time up to nearest 15 minutes

CEILING can be used to round time up to a given multiple. For example, to round a time in A1 down to the nearest 15 minutes, you can use CEILING like this:

=CEILING(A1,"0:15") // round up to nearest 15 min

Rounding functions in Excel

Excel provides a number of functions for rounding:

Notes

  • CEILING works like the MROUND function, but CEILING always rounds up.
  • If number is an exact multiple of significance, no rounding occurs.
  • If number and significance are both negative, CEILING rounds down, away from zero.
  • If number is negative, and significance is positive, CEILING rounds up, towards zero.
  • For more control over how CEILING rounds negative numbers, see the CEILING.MATH function.