Ceiling Formula
This function rounds
a number up to the nearest multiple specified by the user.
Click on images to enlarge...
|
|
|
|
|
|
|
|
|
|
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
|
|
|
|||