CEILING in Excel: Signed Multiples and Rounding Direction
Round to a chosen multiple with CEILING, including negative numbers with positive or negative significance, and distinguish the current contract from CEILING.PRECISE.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
CEILING rounds a number to a multiple of a specified step. For positive quantities and a positive step, it finds the next multiple at or above the quantity. For negative quantities, the sign of significance changes the direction, so “always away from zero” is not a sufficient description of the current documented contract.
Syntax and sign rules
=CEILING(number, significance)
Both arguments are required. number is the quantity; significance defines the step and, for a negative number, its sign affects direction. Current Microsoft documentation states that negative number/negative significance rounds away from zero, while negative number/positive significance rounds toward zero. An exact multiple is unchanged. Nonnumeric arguments produce documented #VALUE!. Current CEILING reference
The examples here use positive significance for positive quantities, and both documented signs for negative quantities. They do not establish every zero-step, positive-number/negative-step or historical-release boundary.
Step-by-step packing example
Suppose a fictional stock request is 101 units, and packages hold 25 units. Enter 101 in A2 and 25 in B2:
=CEILING(A2,B2)
The independent expectation is 125 units. Multiples around the request are 100 and 125. The smaller one is insufficient; 125 is the next available package total. The corresponding package count is 125/25 = 5, leaving 24 units beyond the request.
Keep units straight: the function returns a rounded quantity, not the number of packages. Dividing by the package size converts the total to a count. If partial packages are permitted, a different business rule may be appropriate.
Signed controls
| Formula | Independent expectation | Reason |
|---|---|---|
=CEILING(101,25) |
125 | Next positive multiple |
=CEILING(100,25) |
100 | Already a multiple |
=CEILING(17,5) |
20 | Positive upward rounding |
=CEILING(-17,5) |
−15 | Negative number moves toward zero |
=CEILING(-17,-5) |
−20 | Negative number moves away from zero |
=CEILING(-20,5) |
−20 | Exact multiple stays unchanged |
=CEILING(7.31,0.25) |
7.5 | Next quarter-unit step |
For −17, the neighboring multiples of five are −20 and −15. The first is farther from zero; the second is numerically greater. This distinction is useful when reviewing spreadsheets that describe signed adjustments as simply “round up.” Record which meaning is intended.
For positive n and positive step s, the mathematical model is s×ceil(n/s). For negative n and positive s, the same mathematical ceiling gives the toward-zero value. For negative n and negative s, the current documented rule instead chooses the farther-from-zero multiple. These are models of the cited cases, not reverse-engineered claims about Excel’s internal arithmetic.
Practical case: round once or round each line?
Two separate requirements of 101 and 24 units illustrate a grouping decision. Rounding each independently to 25-unit packages gives 125 + 25 = 150. Combining first gives 101 + 24 = 125, already a multiple, so rounding the combined total gives 125.
=CEILING(101,25)+CEILING(24,25)
=CEILING(101+24,25)
The 25-unit difference is a consequence of grouping, not an error in the rounding function. If items cannot share packages, line-by-line rounding is relevant; if they can, pooling may use fewer packages. The spreadsheet formula should express the actual rule rather than silently choosing one.
Troubleshooting and related choices
Check that the step is in the same units as the quantity. A step of 0.25 means quarter units, not two decimal places. For decimal-place rounding, consider ROUND; for the nearest multiple rather than a directional multiple, see MROUND.
Decimal numbers are not all represented exactly in binary arithmetic. The decimal examples are independent reference expectations; they do not certify boundary behavior for tiny steps, huge ratios or values within floating-point noise of a multiple. Validate such cases against the intended precision policy.
CEILING.PRECISE is a separate contract. Do not replace signed-significance formulas solely because the names look similar; first compare negative inputs. Historical Excel behavior and the release in which any sign rule changed have not been established here. The current reference is the scope of these examples.
Availability and evidence
Microsoft’s dedicated reference, checked October 18, 2026, lists Excel for Microsoft 365 on Windows and Mac, Excel 2024 on Windows and Mac, Excel 2021 on Windows and Mac, Excel 2019 and Excel 2016. This is the documented product scope, not a record of execution on those releases. First introduction and last functioning build are unknown; the product list does not establish either. The reference continues to document this function; it does not establish a retirement date.
The examples here are independently calculated or explicitly identified as documented expectations. No Excel application or other spreadsheet engine was run, and no native screenshots are supplied. Very large, ill-conditioned or locale-sensitive inputs require separate validation.