The CEILING function with example in Excel

The CEILING function in Excel is used to round a number up to the nearest multiple of a specified significance. This can be particularly useful when you need to round numbers to a certain level of precision, such as the nearest integer, nearest tenth, nearest five, etc.

Syntax

scss
CEILING(number, significance)

• number: The number you want to round up.
• significance: The multiple to which you want to round the number.

Example

Suppose you have a list of numbers, and you want to round them up to the nearest 10.

Data:

A	B
Number	Rounded Up
12.34
26.75
32.19
45.67
58.92

Steps to Use the CEILING Function:

1. Enter the Numbers:

   • In cells A2 to A6, enter the numbers: 12.34, 26.75, 32.19, 45.67, 58.92.

2. Use the CEILING Function:

   • In cell B2, enter the following formula:

     scss
     =CEILING(A2, 10)
     

   • Drag the fill handle from B2 down to B6 to apply the formula to the other cells in column B.

Result:

After entering the formula and dragging the fill handle, the rounded numbers will be displayed in column B.

A	B
Number	Rounded Up
12.34	20
26.75	30
32.19	40
45.67	50
58.92	60

Explanation

• =CEILING(A2, 10) rounds 12.34 up to the nearest multiple of 10, which is 20.
• =CEILING(A3, 10) rounds 26.75 up to the nearest multiple of 10, which is 30.
• =CEILING(A4, 10) rounds 32.19 up to the nearest multiple of 10, which is 40.
• =CEILING(A5, 10) rounds 45.67 up to the nearest multiple of 10, which is 50.
• =CEILING(A6, 10) rounds 58.92 up to the nearest multiple of 10, which is 60.

Additional Examples

Example with Different Significance

You can also round to other multiples, such as 0.1 or 5.

1. Round Up to the Nearest 0.1:

   • In cell C2, enter:

     scss
     =CEILING(A2, 0.1)
     

   • Drag the fill handle from C2 down to C6.

   This will round each number up to the nearest tenth.

2. Round Up to the Nearest 5:

   • In cell D2, enter:

     scss
     =CEILING(A2, 5)
     

   • Drag the fill handle from D2 down to D6.

   This will round each number up to the nearest multiple of 5.

Results for Additional Examples:

A	B	C	D
Number	Rounded Up	Rounded to 0.1	Rounded to 5
12.34	20	12.4	15
26.75	30	26.8	30
32.19	40	32.2	35
45.67	50	45.7	50
58.92	60	59.0	60

Summary

The CEILING function in Excel is useful for rounding numbers up to the nearest specified multiple. This function helps ensure numbers are rounded up in a consistent manner, which can be particularly useful in financial calculations, inventory management, and other areas requiring precise control over rounding.