Summary
The Excel CEILING function is used to round a number up to the nearest multiple of significance. This function is particularly useful in financial calculations where rounding up to a specific denomination is necessary, or in engineering and construction where materials are sold in specific increments.
Syntax
=CEILING(number, significance)
- number: The value you want to round.
- significance: The multiple to which you want to round the number.
Return value
A number rounded up to the nearest multiple of the specified significance.
How to use
Use CEILING to round a number up to the nearest multiple of the specified significance. Provide the number to be rounded as the first argument and the multiple (significance) as the second argument.
Examples
Simple CEILING
Rounding Up to Nearest Multiple: To round a number up to the nearest multiple of 5:
=CEILING(7, 5)
This formula rounds the number 7 up to the nearest multiple of 5, resulting in 10.
CEILING in Financial Calculations
Rounding Prices to Nearest Dollar: To round a price up to the nearest whole dollar:
=CEILING(49.25, 1)
Rounds the amount $49.25 up to the nearest dollar, resulting in $50.
CEILING for Material Quantities
Calculating Material Quantities in Construction: To determine the number of materials needed, rounded up to the nearest 100 units:
=CEILING(550, 100)
This rounds 550 units up to the nearest 100 units, yielding 600 units.
CEILING with Fractional Significance
Rounding Up to Nearest Fractional Value: To round a number up to the nearest quarter (.25):
=CEILING(10.1, 0.25)
This rounds 10.1 up to the nearest quarter, resulting in 10.25.
CEILING for Time Calculations
Rounding Time to Nearest Hour in Scheduling: To round time (in hours) up to the nearest half-hour:
=CEILING(A2, 0.5)
Assuming A2 contains a time in hours, this formula rounds it up to the nearest half-hour.
Additional Notes
- CEILING is commonly used in financial, engineering, and construction scenarios.
- It’s important to note that CEILING always rounds numbers up, even if the number is already a multiple of the significance.
- The function can handle both positive and negative numbers, but the sign of the significance should match the sign of the number for expected results.