How to Use the CEILING.PRECISE and FLOOR.PRECISE Functions in Microsoft Excel for Exact Multiple Rounding

Rounding numbers to the nearest integer or specific decimal place is commonly achieved using ROUND, ROUNDUP, or ROUNDDOWN. However, operational modeling—such as packaging goods into standard carton quantities, scheduling shift intervals, or establishing currency price brackets—frequently demands rounding numbers to arbitrary multiples (such as the nearest multiple of 5, 25, or 0.25). While Excel includes older functions like CEILING and FLOOR, Microsoft introduced CEILING.PRECISE and FLOOR.PRECISE to streamline calculations by eliminating sign conflicts when handling negative values.

Understanding CEILING.PRECISE and FLOOR.PRECISE

In older legacy functions (like CEILING(number, significance)), supplying a negative number alongside a positive significance resulted in a #NUM! calculation error. Resolving this required matching the sign of the significance parameter to the sign of the input number.

CEILING.PRECISE and FLOOR.PRECISE overcome this limitation entirely:

• The significance argument always operates using its absolute value; its sign is completely ignored.
• CEILING.PRECISE: Always rounds the number up towards positive infinity (or to zero for negative numbers) to the nearest multiple of significance.
• FLOOR.PRECISE: Always rounds the number down towards negative infinity to the nearest multiple of significance.
• If significance is omitted, both functions default to 1, rounding to the nearest integer.

Syntax and Parameter Breakdown

The syntax for both functions is concise and identical:

CEILING.PRECISE(number, [significance])
FLOOR.PRECISE(number, [significance])

The arguments operate as follows:

• number: Required. The real number or cell reference you wish to round.
• significance: Optional. The multiple to which you wish to round. If omitted, the default significance is 1.

Practical Example 1: Packaging Goods into Case Sizes

A manufacturing warehouse produces component parts that must be boxed and shipped in crates holding exactly 24 units. If an order specifies 170 units, the warehouse must round up to the next full crate count to avoid splitting boxes.

Set up the parameters in your spreadsheet:

• Cell A2 (Units Ordered): 170
• Cell B2 (Crate Size): 24

To determine the total units shipped, apply CEILING.PRECISE in cell C2:

=CEILING.PRECISE(A2, B2)

Excel calculates the next multiple of 24 above 170, returning 192 units (exactly 8 full crates). To get the number of physical crates needed, divide by the crate size: =CEILING.PRECISE(A2, B2) / B2.

Practical Example 2: Financial Price Steps and Discount Buckets

Retail pricing or financial trading often requires rounding down to defined price increments, such as 50 pence (£0.50):

• Cell A5 (Calculated Cost): £14.85
• Cell B5 (Price Increment): 0.50

To find the base floor pricing bracket, use FLOOR.PRECISE in cell C5:

=FLOOR.PRECISE(A5, B5)

Excel rounds down to £14.50.

Handling Negative Numbers: Behaviour and Directionality

Understanding mathematical direction is vital when working with negative numbers across accounting variance models:

Formula	Result	Mathematical Explanation
=CEILING.PRECISE(4.3, 2)	6	Rounds up towards positive infinity to next multiple of 2
=CEILING.PRECISE(-4.3, 2)	-4	Rounds up towards zero/positive infinity (-4 > -4.3)
=FLOOR.PRECISE(4.7, 2)	4	Rounds down towards negative infinity
=FLOOR.PRECISE(-4.3, 2)	-6	Rounds down towards negative infinity (-6 < -4.3)

Comparison: CEILING.PRECISE vs CEILING.MATH and MROUND

To choose the correct rounding function for your financial models:

• CEILING.PRECISE / FLOOR.PRECISE: Consistent directional rounding (always towards $+\infty$ for ceiling and $-\infty$ for floor). Completely sign-agnostic significance. Ideal for deterministic engineering and supply chain logic.
• CEILING.MATH / FLOOR.MATH: Allows an optional third parameter (mode) that lets you choose whether negative numbers round towards zero or away from zero.
• MROUND: Rounds to the nearest multiple (either up or down depending on whichever multiple is closer). Requires matching signs between number and multiple, otherwise throwing #NUM!.