While the standard ROUND function in Microsoft Excel pushes decimals to the nearest whole number based on strict mathematical rules (e.g., 2.4 becomes 2, while 2.6 becomes 3), some business scenarios require you to round numbers to specific multiples. For example, if you sell products in bulk boxes of 12, and a customer calculates they need 45 individual items, you cannot sell them 3.75 boxes. You must force the calculation to round up to the nearest multiple of 12 (which is 48 items, or 4 boxes). You can automate this complex logic using the CEILING and FLOOR functions.
Using the CEILING Function (Rounding Up)
The CEILING function forces Excel to ignore standard rounding rules and aggressively round a number up to the next nearest specified multiple.
The syntax is: =CEILING(number, significance)
- number: The cell containing the messy data (e.g., 45 items).
- significance: The multiple you want to round to (e.g., 12).
If you type =CEILING(45, 12), Excel will calculate that 45 is not a multiple of 12. It will push the number upward until it hits 48 (which is 12 x 4), completely ignoring the fact that 45 is technically closer to 36 (12 x 3). This is perfect for pricing models; if you want all your retail prices to end in 99 cents, you can use =CEILING(A2, 1) - 0.01 to round a messy $14.33 price tag forcefully up to $14.99.
Using the FLOOR Function (Rounding Down)
The FLOOR function is the exact opposite of CEILING. It aggressively pulls a number down to the nearest specified multiple.
The syntax is identical: =FLOOR(number, significance)
Imagine you have a company policy that only allows employees to claim travel expenses in exact $50 increments. If an employee submits a receipt for $138, you can type =FLOOR(138, 50). Excel will aggressively pull the number downward until it hits $100 (which is 50 x 2). Even though $138 is much closer to $150, the FLOOR function strictly prevents the number from ever going up.
Modern Alternatives: CEILING.MATH and FLOOR.MATH
In modern versions of Excel, Microsoft introduced CEILING.MATH and FLOOR.MATH. These function identically to the legacy commands but handle negative numbers much better. If you are applying these formulas to accounting spreadsheets that contain negative debt balances, it is highly recommended to use the newer .MATH variants to avoid calculation errors.