Share in Facebook


03 March 2019

Ceiling Function


In this article I will discuss about CEILING function, another Mathematical function in Microsoft Excel.


In my last article we have discussed about Floor Function which is used to round down a number in respect to given significance. 

For example, suppose Angelina Jolie earns $44,019 per day and we need to provide another incentive value on this daily earnings which will be calculated on the multiplication of $1000  rounded down (which is 'significance' in Excel).

Here we need to use Floor function and the formula will be =FLOOR(C9,1000). (Where C9 cell contains $44,019).










Which means we have rounded down the value towards zero / '0'.

Now if we need to calculate the same incentive value but in rounded up i.e., away from zero and in multiplication of $1000 (significance), then we must use CEILING Function.

We will use Ceiling function in Excel in this article but before that let us first understand the syntax of Ceiling function.

How to use Ceiling Function ?

Syntax
The syntax for the CEILING function in Microsoft Excel is same as Floor function,

=CEILING(number, significance)

Parameters or Arguments

number
The number that you wish to round up.

Significance
The multiple of significance that you wish to round a number to.

Example :

We will use an example to understand how to use CEILING function.

Suppose, we have a list of a sales team with their monthly sales. Each sales representative is allotted with the incentive price for every $1000 sales which is 5% of the relevant sales amount, now we need to calculate the incentive amount that will be paid to the representative as an incentive at the end of the month but the incentive will be on higher / rounded up of sales value.

So if we tabulate this problem, it will be like below.

How to use Ceiling Function in Excel
How to use Ceiling Function in Excel


To find the relevant sales which have to be the farthest multiple of 1000 (away from zero), we will use the CEILING function in excel with a nearest factor of 1000.

The formula will be : =CEILING(C3,1000)

How to use Ceiling Function in Excel
How to use Ceiling Function in Excel


The first part of the problem is done, now we will also calculate the second part of the said problem by simply multiplying it by 5%.










Hope you have fully understood where and how to use Ceiling function, in the above example we used CEILING function in excel to deal with significant value and it is also useful in calculations for currency conversions, discounts etc.

How to use Ceiling Function in Excel
How to use Ceiling Function in Excel


Note : If you notice carefully you will discover that in FLOOR function the result is lower than the actual value and in CEILING  function the value gets increased to reach the significance.


Hope you have enjoyed reading this article ...
Keep reading, sharing & Stay blessed ...

No comments: