CEILING function

The CEILING function in Excel is used to round a number up, away from zero, to the nearest multiple of a specified significance (which can be a number or a multiple of a number). It is useful when you need to round numbers in a consistent way, such as rounding up to the nearest 5, 10, or 100.

Syntax

=CEILING(number, significance)

Parameters

• number: The number that you want to round.
• significance: The multiple to which you want to round the number. For example, you could round the number to the nearest 10, 0.5, or 100, depending on the value of significance.

Return Value

The function returns the rounded number, rounded up to the nearest multiple of the significance.

How It Works

• The CEILING function rounds numbers up to the nearest specified multiple. If the number is already a multiple of the specified significance, it returns the number itself.
• It always rounds up, regardless of whether the number is closer to the lower or higher multiple of significance.

Examples

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

   =CEILING(17, 10)
   

   Result: 20

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

   =CEILING(12.3, 0.5)
   

   Result: 12.5

3. Round Up to Nearest 100 To round the number 275 up to the nearest multiple of 100:

   =CEILING(275, 100)
   

   Result: 300

4. Round Up Negative Numbers If the number is negative, it will still round away from zero. For example, to round -8 to the nearest multiple of 3:

   =CEILING(-8, 3)
   

   Result: -6

Important Notes

• If the significance is positive, the CEILING function will round up toward the higher value.
• If the significance is negative, the function will round away from zero, rounding toward the lower value.
• The CEILING function rounds up, unlike the FLOOR function, which rounds down toward zero.

Use Cases

• Financial Calculations: Rounding up amounts in currency, especially when prices need to be rounded up to a certain denomination.
• Scheduling or Timing: When time or tasks need to be rounded to the nearest unit of measure (e.g., round time up to the nearest hour, day, or week).
• Data Analysis: Rounding data to the nearest multiple for easier grouping or analysis, such as rounding ages, measurements, or values in specified increments.