CEILING.MATH function

The CEILING.MATH function in Excel is used to round a number up to the nearest integer or specified multiple, similar to the CEILING function, but with more control over how negative numbers are handled and additional options for rounding.

Syntax

=CEILING.MATH(number, [significance], [mode])

Parameters

• number: The number 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).
• [mode] (optional): A number that determines the rounding behavior for negative numbers. If omitted, it defaults to 1, which rounds away from zero.

Return Value

The function returns the number rounded up to the nearest multiple of the significance or integer (depending on the optional parameters). If the number is already a multiple of significance, it will return the number itself.

How It Works

• CEILING.MATH rounds the number up, away from zero, to the nearest specified multiple of significance.
• For negative numbers, the rounding direction depends on the mode parameter. By default, negative numbers will round away from zero (round toward the more negative value).
• If mode is set to 0, negative numbers will round toward zero.

Examples

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

   =CEILING.MATH(17.3, 10)
   

   Result: 20

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

   =CEILING.MATH(12.3)
   

   Result: 13

3. Round Up Negative Number with Default Mode To round the negative number -17.3 up to the nearest multiple of 10 (round away from zero):

   =CEILING.MATH(-17.3, 10)
   

   Result: -10 (since it rounds away from zero)

4. Round Up Negative Number Toward Zero To round the negative number -17.3 up to the nearest multiple of 10 (round toward zero) by setting the mode to 0:

   =CEILING.MATH(-17.3, 10, 0)
   

   Result: -20 (rounding toward zero)

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

   =CEILING.MATH(12.3, 0.5)
   

   Result: 12.5

Important Notes

• Default Significance: If the significance argument is omitted, it defaults to 1, meaning it will round to the nearest integer.
• Handling Negative Numbers: The mode argument gives you more flexibility in how negative numbers are rounded. The default mode (1) rounds negative numbers away from zero, but you can set it to 0 to round them toward zero.
• Flexibility: Unlike the CEILING function, CEILING.MATH can handle both positive and negative numbers with more control, making it a more versatile rounding function.

Use Cases

• Financial Calculations: Rounding prices up to a specified denomination (e.g., rounding up prices in increments of 5 or 10).
• Data Analysis: Rounding values for analysis that need to be grouped by specific multiples or intervals, especially with negative numbers.
• Time Calculations: Rounding time intervals (such as hours, minutes) up to the nearest desired increment.