When running a retail business or managing inventory spreadsheets, standard rounding formulas like ROUND or ROUNDUP are often insufficient. If you calculate a new product price to be £14.32, you likely do not want to round it up to £15.00, nor do you want to leave it at an awkward decimal. Instead, you might want all your prices to end in a clean multiple of £0.05 or £0.99. To force Excel to round numbers upwards to a specific, custom multiple, you need to use the highly versatile CEILING function.
What Does the CEILING Function Do?
Unlike standard rounding functions that dictate the number of decimal places, the CEILING function evaluates a number and rounds it upwards to the nearest integer or multiple of significance that you specify. Whether you want to round manufacturing times up to the nearest 15 minutes or round prices up to the nearest 10 pence, CEILING handles the mathematics instantly.
Understanding the Syntax
The CEILING function requires two mandatory arguments:
=CEILING(number, significance)
- number: The raw value or cell reference you want to round.
- significance: The specific multiple to which you want the number rounded.
Basic Price Rounding Examples
Imagine you have imported a wholesale price list into Excel. You want to apply a 20% markup, but you want the final retail prices to look clean and professional by ending in a multiple of 5 pence.
- Open your Excel workbook.
- Assume cell A2 contains the wholesale price of £11.41.
- To add a 20% markup and round the result to the nearest £0.05, click into cell B2.
- Type the following formula:
=CEILING(A2*1.20, 0.05)
- Press Enter.
The raw calculation of £11.41 * 1.20 equals £13.692. The CEILING function evaluates this raw number and rounds it up to the nearest multiple of 0.05. The final output in cell B2 will be perfectly formatted as £13.70.
Rounding Inventory and Manufacturing Units
The function is equally powerful for whole numbers. If you are calculating how many sheets of plywood you need for a construction project, you cannot purchase 4.2 sheets. You must buy entire sheets, and perhaps they are only sold in bundles of 3.
If cell C2 contains your raw requirement of 4.2 sheets, you can force Excel to round up to the nearest multiple of 3 using this formula:
=CEILING(C2, 3)
Excel will instantly output 6, guaranteeing you purchase the correct number of bundles without running short.
The Difference Between CEILING and FLOOR
It is important to remember that CEILING always rounds numbers away from zero (upwards for positive numbers). If you want to achieve the exact opposite effect—rounding numbers down to the nearest multiple of significance to create discount pricing or conservative estimates—you simply swap the function for its counterpart, FLOOR, using the exact same syntax.