# CEILING Function in Excel

### π Part 1: Introduction

#### Definition

The CEILING function in Microsoft Excel rounds a number up, away from zero, to the nearest multiple of a specified significance.

#### Purpose

The primary purpose of the CEILING function is to round values up to a desired level of precision or significance. For instance, if you wish to avoid using pennies in your prices and your product is priced at \$4.42, you can use the CEILING function to round prices up to the nearest nickel.

#### Syntax & Arguments

syntax
`CEILING(number, significance)`
• Number: The value you want to round.
• Significance: The multiple to which you want to round.

#### Return Value

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

#### Remarks

• If either argument is nonnumeric, CEILING returns the #VALUE! Error value.
• Regardless of the sign of the number, a value is rounded up when adjusted away from zero.
• If the number is an exact multiple of significance, no rounding occurs.
• If the number and significance are negative, the value is rounded down, away from zero.
• The value is rounded up towards zero if the number is negative and the significance is positive.

### π Part 2: Examples

π Example 1

• Purpose: Rounding up sales figures to the nearest ten.
• Data & Formulas:
ABC
1SalesFormulaResult
2152.5=CEILING(A2, 10)160
3167.3=CEILING(A3, 10)170
4141.2=CEILING(A4, 10)150
• Explanation: This example demonstrates how to round up sales figures to the nearest ten using the CEILING function. It’s useful for businesses that prefer to deal with rounded numbers for more straightforward calculations and reporting.

π Example 2

• Purpose: Rounding up inventory levels to the nearest 50.
• Data & Formulas:
ABC
1InventoryFormulaResult
2112=CEILING(A2, 50)150
343=CEILING(A3, 50)50
4267=CEILING(A4, 50)300
• Explanation: This example demonstrates how to round up inventory levels to the nearest 50 units using the CEILING function. This can be useful for businesses that order or produce goods in batches of a specific size.

π Example 3

• Purpose: Rounding up employee working hours to the nearest half-hour.
• Data & Formulas:
ABC
1HoursFormulaResult
28.2=CEILING(A2, 0.5)8.5
36.7=CEILING(A3, 0.5)7
45.1=CEILING(A4, 0.5)5.5
• Explanation: By using the CEILING function, businesses can round up employees’ working hours to the nearest half-hour. This can simplify payroll calculations, especially when overtime or special rates apply.

π Example 4

• Purpose: Rounding up product weights to the nearest kilogram.
• Data & Formulas:
ABC
1Weight (kg)FormulaResult
22.3=CEILING(A2, 1)3
34.8=CEILING(A3, 1)5
46.2=CEILING(A4, 1)7
• Explanation: This example showcases how businesses can use the CEILING function to round up product weights to the nearest kilogram. This can benefit packaging, shipping, or when selling products by weight.

π Example 5

• Purpose: Rounding up discount percentages to the nearest 5%.
• Data & Formulas:
ABC
1Discount %FormulaResult
27%=CEILING(A2, 5%)10%
312%=CEILING(A3, 5%)15%
43%=CEILING(A4, 5%)5%
• Explanation: This example demonstrates how businesses can round up discount percentages to the nearest 5% using the CEILING function. It’s useful for companies that offer tiered discounts or want to maintain consistent discount levels.

π Example 6: Using CEILING with IF

• Purpose: Determine if the rounded-up value of a number exceeds a certain threshold.
• Data & Formulas:
ABC
1ValueFormulaResult
242.3=IF(CEILING(A2, 10)>50, “Exceeds”, “Safe”)Safe
358.7=IF(CEILING(A3, 10)>50, “Exceeds”, “Safe”)Exceeds
449.1=IF(CEILING(A4, 10)>50, “Exceeds”, “Safe”)Exceeds
• Explanation: This example uses the CEILING function nested within an IF function to determine if the rounded-up value of a number exceeds a threshold of 50. It can be helpful in scenarios where certain operations or decisions are contingent upon a value surpassing a limit.

π Example 7: Using CEILING with SUM

• Purpose: Sum the rounded-up values of multiple numbers.
• Data & Formulas:
ABC
1ValueFormulaResult
223.5=CEILING(A2, 5)25
327.3=CEILING(A3, 5)30
4=SUM(B2:B3)55
• Explanation: Using the CEILING function in conjunction with the SUM function, we can aggregate the rounded-up values of multiple numbers. This can be useful in scenarios where cumulative measurements are required, such as financial reporting.

π Example 8: Using CEILING with VLOOKUP

• Purpose: Round a specific value from a table to the nearest ten.
• Data & Formulas:
ABCDE
1IDValueFormulaResult
210136.2
310244.5
410352.7
5102=CEILING(VLOOKUP(B5, A2:B4, 2, FALSE), 10)50
• Explanation: This example demonstrates using the CEILING function with the VLOOKUP function to round up a specific value from a table to the nearest ten. It’s beneficial when you have a large dataset and need to quickly retrieve and compute a value.

π Example 9: Using CEILING with AVERAGE

• Purpose: Calculate the average rounded-up value from multiple numbers.
• Data & Formulas:
ABC
1ValueFormulaResult
212.2=CEILING(A2, 1)13
314.3=CEILING(A3, 1)15
4=AVERAGE(B2:B3)14
• Explanation: This example calculates the average rounded-up value from multiple numbers. It’s useful in scenarios requiring an average measurement, such as market research analyses.

π Example 10: Using CEILING with MAX

• Purpose: Determine the maximum rounded-up value from a set of numbers.
• Data & Formulas:
ABC
1ValueFormulaResult
27.1=CEILING(A2, 1)8
38.2=CEILING(A3, 1)9
4=MAX(B2:B3)9
• Explanation: This example determines the maximum rounded-up value from a set of numbers using the CEILING function with the MAX function. This can be beneficial in scenarios where peak values are of interest.

### π Part 3: Tips and Tricks

1. Always ensure that the significance is not set to zero to avoid errors.
2. The CEILING function can be beneficial in financial scenarios where rounding to specific denominations (like the nearest dollar or nickel) is required.
3. Remember the significance sign when working with negative numbers to get the desired rounding direction.