Excel CEILING Function - Easy Excel Tutorial

Introduction:

The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function, but CEILING always rounds up.

This function rounds a number up to the nearest multiple specified by the user.

Syntax

'=CEILING(ValueToRound,MultipleToRoundUpTo)

The ValueToRound can be a cell address or a calculation.

Arguments 

·         number - The number that should be rounded.

·         multiple - The multiple to use when rounding.

Usage notes 

The Excel CEILING function rounds a number up to a given multiple.

Example 1

The following table was used by an estate agent renting holiday apartments. The properties being rented are only available on a weekly basis.

When the customer supplies the number of days required in the property the =CEILING()

function rounds it up by a multiple of 7 to calculate the number of full weeks to be billed.

Example 2

The following table was used by a builder’s merchant delivering products to a construction site. The merchant needs to hire trucks to move each product. Each product needs a particular type of truck of a fixed capacity.

Table 2.1 calculates the number of trucks required by dividing the Units to Be Moved by the Capacity of the truck.

This result of the division are not whole numbers, and the builder cannot hire just part of a truck.

Table 2.2 shows how the =CEILING() function has been used to round up the result of the division to a whole number, and thus given the exact amount of trucks needed.

Example 3

The following tables were used by a shopkeeper to calculate the selling price of an item. The shopkeeper buys products by the box. The cost of the item is calculated by dividing the Box Cost by the Box Quantity.

The shopkeeper always wants the price to end in 99 pence.

Table 3.1 shows how just a normal division results in varying Item Costs.

Table 3.2 shows how the =CEILING() function has been used to raise the Item Cost to always end in 99 pence.

Explanation

=INT(E83)                          Calculates the integer part of the price.

=MOD(E83,1)                      Calculates the decimal part of the price.

=CEILING(MOD(E83),0.99)    Raises the decimal to 0.99