Saturday, October 16, 2021

Learn How to Use Ceiling Formula excel in English.

CEILING

what is ceiling formula in excel

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                                         

No comments:

Post a Comment