EXCEL FUNCTIONS-CEILING

INTRODUCTION

CEILING FUNCTION has become redundant now and is only given here for the friends who has Excel older than 2010.

CEILING.MATH is the new function which has taken its place. CLICK HERE TO READ CEILING.MATH

CEILING  is quite a useful function in day to day working in programming. It is a special type of ROUND function.

It is found under the MATH AND TRIG GROUP of the formulas in MICROSOFT EXCEL.

CEILING function rounds up the number to its nearest multiple of significance as mentioned. [suppose significance is 2, then it’ll round the given number to the nearest multiple of 2]

CEILING FUNCTION is very useful when we need to round up some number to a custom significance such as rounding some number to the nearest 0.2 significance or anything like that.

PURPOSE OF CEILING FUNCTION IN EXCEL

CEILING function rounds up the number to the next integer  to the next nearest multiple of significance.

*THE SIGNS OF NUMBER AND SIGNIFICANCE SHOULD BE SAME OTHERWISE IT’LL CAUSE AN ERROR.

PREREQUISITES TO LEARN CEILING FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  • Some information about the MATH & TRIG terms is an advantage for the use of such formulas.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: CEILING FUNCTION

The Syntax for the function is

=CEILING.MATH( REFERENCE/VALUE, SIGNIFICANCE)

REFERENCE/VALUE is the cell address or the value, which is to be rounded.

SIGNIFICANCE is the number , to which we want to round up our number.

The number will be rounded up to the nearest multiple of significance.

EXAMPLE:CEILING FUNCTION IN EXCEL

DATA SAMPLE

Let us take a random data to check the FLOOR FUNCTION to various significance.

we’ll round a few numbers of different types to the nearest multiple of significance.

  • ROUND 23.2 TO THE NEAREST MULTIPLE OF 1.5
  • ROUND  -20 TO THE NEAREST MULTIPLE OF -3
  • ROUND  -20 TO THE NEAREST MULTIPLE OF 3
USING CEILING FUNCTION IN EXCEL

STEPS TO USE CEILING.MATH FUNCTION-EXAMPLE

STEPS:

1. Place the cursor in the cell and type the following function

2. USE THE FOLLOWING FUNCTION

FOR

  • ROUND 23.2 TO THE NEAREST MULTIPLE OF 1.5
  • =CEILING(23.2,1.5)
  • This example shows the simple use of CEILING FUNCTION. The number to be rounded is 23.2 and it is to be rounded with the significance of 1.5. So the rounding goes upto 24 and hence the answer.
  • ROUND  -20 TO THE NEAREST MULTIPLE OF -3
  • =CEILING(-20,-3)
  • In this example, we want to round the number -20 to the significance of -3. The answer comes to be -21.
  • ROUND  -20 TO THE NEAREST MULTIPLE OF 3
  • =CEILING(-20,3)
  • In this example we make the significance positive and it works to  round towards the 0 and -18 is the answer.
  • NOTE-THE LAST EXAMPLE WILL CREATE AN ERROR IF TRIED IN EXCEL VERSIONS EARLIER TO 2010.
  • As we already mentioned both signs should be same for old EXCEL software.

GENERALIZED STEPS TO USE CEILING FUNCTION

HERE ARE THE STEPS TO USE COUNT FUNCTION   

PLACE YOUR CURSOR IN THE CELL WHERE YOU WANT THE RESULT.

  • USE THE FUNCTION
  • =CEILING(NUMBER, SIGNIFICANCE)
  • Press ENTER and the result will appear.