Use of 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
| ||||||
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें