### CEILING EXCEL FORMULA

EXCEL FORMULA | |||

Function Dictionary | Function Examples | Function Categories | |

## CEILING |
||||||||

Number | Raised Up | |||||||

2.1 | 3 | =CEILING(C4,1) | ||||||

1.5 | 2 | =CEILING(C5,1) | ||||||

1.9 | 2 | =CEILING(C6,1) | ||||||

20 | 30 | =CEILING(C7,30) | ||||||

25 | 30 | =CEILING(C8,30) | ||||||

40 | 60 | =CEILING(C9,30) | ||||||

What Does It Do ? | ||||||||

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. | ||||||||

Formatting | ||||||||

No special formatting is needed. | ||||||||

Example 1 | ||||||||

The following table was used by a 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. | ||||||||

Days Required | Days To Be Billed |
|||||||

Customer 1 | 3 | 7 | =CEILING(D28,7) | |||||

Customer 2 | 4 | 7 | =CEILING(D29,7) | |||||

Customer 3 | 10 | 14 | =CEILING(D30,7) | |||||

Example 2 | ||||||||

The following table was used by a builders 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 1 calculates the number of trucks required by dividing the Units To Be Moved by | ||||||||

the Capacity of the truck. | ||||||||

This results of the division are not whole numbers, and the builder cannot hire just part | ||||||||

of a truck. | ||||||||

Table 1 | ||||||||

Item | Units To Be Moved |
Truck Capacity |
Trucks Needed |
|||||

Bricks | 1000 | 300 | 3.33 | =D45/E45 | ||||

Wood | 5000 | 600 | 8.33 | =D46/E46 | ||||

Cement | 2000 | 350 | 5.71 | =D47/E47 | ||||

Table 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. | ||||||||

Table 2 | ||||||||

Item | Units To Be Moved |
Truck Capacity |
Trucks Needed |
|||||

Bricks | 1000 | 300 | 4 | =CEILING(D54/E54,1) | ||||

Wood | 5000 | 600 | 9 | =CEILING(D55/E55,1) | ||||

Cement | 2000 | 350 | 6 | =CEILING(D56/E56,1) | ||||

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 1 shows how just a normal division results in varying Item Costs. | ||||||||

Table 1 | ||||||||

Item | Box Qnty | Box Cost | Cost Per Item | |||||

Plugs | 11 | £20 | 1.81818 | =D69/C69 | ||||

Sockets | 7 | £18.25 | 2.60714 | =D70/C70 | ||||

Junctions | 5 | £28.10 | 5.62000 | =D71/C71 | ||||

Adapters | 16 | £28 | 1.75000 | =D72/C72 | ||||

Table 2 shows how the =CEILING() function has been used to raise the Item Cost to | ||||||||

always end in 99 pence. | ||||||||

Table 2 | ||||||||

Item | In Box | Box Cost | Cost Per Item | Raised Cost | ||||

Plugs | 11 | £20 | 1.81818 | 1.99 | ||||

Sockets | 7 | £18.25 | 2.60714 | 2.99 | ||||

Junctions | 5 | £28.10 | 5.62000 | 5.99 | ||||

Adapters | 16 | £28 | 1.75000 | 1.99 | ||||

=INT(E83)+CEILING(MOD(E83,1),0.99) | ||||||||

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 | |||||||