Excel CEILING Function
Summary
The Excel CEILING function returns a given number rounded up to a specified multiple. For example, =CEILING(A1,5) could be used to round a price in A1 to the nearest 5 dollars. 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. For example, the following could be used to round a price in A1 to the nearest 5 dollars.
=CEILING(A1,5)
CEILING can be a can be a useful function to set pricing after currency conversion, discounts, etc. For example, the formula below can round a value in A1 to the nearest 1 dollar, then subtract 1 cent, to get a price like $2.99, $5.99, $49.99, etc.
=CEILING(A1,1) - 0.01
If you need more control over how CEILING rounds negative numbers, see the CEILING.MATH function.
CEILING vs CEILING.MATH
The CEILING.MATH function differs from the CEILING function in these ways:
- CEILING.MATH provides a default multiple of 1 for positive numbers and -1 for negative numbers
- CEILING.MATH provides explicit control for rounding negative numbers (toward zero, away from zero)
- CEILING.MATH appears to use the absolute value of the significance argument. Changing the sign of significance has no effect on the result.
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, the value is rounded down, away from zero.
- If number is negative, and significance is positive, the value is rounded up towards zero.
CEILING formula examples
If you need to round a number up to the nearest specified multiple (i.e. round a number up to the nearest dollar, up to the nearest $.25, up to the nearest multiple of 5, etc.) you can use the CEILING function.
In the...
To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula:
=CEILING(COUNTA($B$5:B5)/size,1)
where "size" is the named range...
To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. In the example...
To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. In the example shown, the formula in C6 is:
=MROUND(B6,"0:15")
How this formula...
To highlight rows in groups of "n" (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions.
In the example shown, the formula...
If you need to round a number up to the next half, you can use the CEILING function, which always rounds up based on a supplied multiple.
Note that MROUND also rounds based on a supplied multiple, but it always rounds...
Next biweekly payday from dateTo get the next payday - assuming a biweekly schedule, with paydays on Friday - you can use a formula based on the CEILING function.
In the example shown, the formula in C6 is:
=CEILING(...
To round up to the next bundle size, you can use the CEILING function which automatically rounds up away from zero.
In the example shown, we need a certain number of items, and the items come in specific bundle sizes....
If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits.
How this formula works
In the example, cell C6 contains this formula:
=MROUND(B6,5)
The...
Related videos
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows - groups of 3 rows, groups of 4 rows, and so on.
Related functions
Good links
