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