CEILING.PRECISE function

The CEILING.PRECISE function in Excel is used to round a number up to the nearest multiple of a specified significance, without considering the sign of the number. Unlike the CEILING function, which rounds away from zero for positive and negative numbers, the CEILING.PRECISE function always rounds away from zero for positive numbers and rounds toward zero for negative numbers.

Syntax

=CEILING.PRECISE(number, [significance])

Parameters

• number: The number that you want to round up.
• [significance] (optional): The multiple to which you want to round the number. If omitted, the default is 1 (rounds to the nearest integer).

Return Value

The function returns the number rounded up to the nearest multiple of the specified significance. If the number is already a multiple of significance, it will return the number itself.

How It Works

• The CEILING.PRECISE function rounds the number up (away from zero for positive numbers) or toward zero for negative numbers, based on the significance value provided.
• It differs from CEILING in how negative numbers are handled: negative numbers are rounded toward zero, not away from zero.

Examples

1. Round Up to Nearest 10 (Positive Number) To round the number 17.3 up to the nearest multiple of 10:

   =CEILING.PRECISE(17.3, 10)
   

   Result: 20

2. Round Up to Nearest Integer (Positive Number) To round the number 12.3 up to the nearest integer:

   =CEILING.PRECISE(12.3)
   

   Result: 13

3. Round Up Negative Number to Nearest 10 To round the negative number -17.3 up to the nearest multiple of 10:

   =CEILING.PRECISE(-17.3, 10)
   

   Result: -10 (rounded toward zero)

4. Round Up Negative Number to Nearest Integer To round the negative number -12.3 up to the nearest integer:

   =CEILING.PRECISE(-12.3)
   

   Result: -12 (rounded toward zero)

5. Round Up to Nearest 0.5 To round the number 12.3 up to the nearest multiple of 0.5:

   =CEILING.PRECISE(12.3, 0.5)
   

   Result: 12.5

Important Notes

• Default Significance: If you don’t specify the significance argument, it defaults to 1, meaning it rounds to the nearest integer.
• Handling Negative Numbers: The key difference from the CEILING function is how negative numbers are rounded. CEILING.PRECISE rounds negative numbers toward zero.
• Use in Financial Calculations: CEILING.PRECISE can be especially useful for rounding financial figures in a consistent manner, without considering the sign of the number.

Use Cases

• Financial Calculations: Rounding monetary values to specific denominations, such as rounding up to the nearest 0.5 or 1.
• Data Grouping: Rounding data to specific intervals for reporting or categorization, particularly when negative values need to be rounded toward zero.
• General Rounding: Any scenario where a consistent rounding direction is required, without the influence of the sign of the number.