EXCEL FUNCTIONS-CEILING.MATH

INTRODUCTION

CEILING.MATH 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.MATH function rounds up the number to the nearest integer or the significance mentioned

CEILING.MATH function is the revised version of CEILING FUNCTION which is available for EXCEL VERSIONS OLDER THAN 2010. We’ll discuss that in the same article.

CEILING.MATH function rounds up to the next integer, if significance is not given. It also gives us the option of choosing if the negative number should go towards zero or away from the zero. We’ll see its working later in the article.

PURPOSE OF CEILING.MATH FUNCTION IN EXCEL

CEILING.MATH function rounds up the number to the next integer (If significance is not given) or to the next nearest multiple of significance.

PREREQUISITES TO LEARN CEILING.MATH 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 aboveWhat Excel does? How to use formula in Excel?

SYNTAX: CEILING.MATH FUNCTION

The Syntax for the function is

=CEILING.MATH( REFERENCE/VALUE, SIGNIFICANCE, MODE)

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

SIGNIFICANCE[optional default +1 for positive number and -1 for negative numbers] is the number , to which we want to round up our number. The number will be rounded up to the nearest multiple of significance.

MODE[optional default is 0] is given as 0 or 1. 0 will round up towards 0 and 1 will round up away from 0. MODE is significant only when our REFERENCE/VALUE  is negative otherwise it has no meaning.

EXAMPLE:CEILING.MATH 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 25 TO THE NEAREST MULTIPLE OF 3
  • ROUND -20 TO THE NEAREST MULTIPLE OF 6 TOWARDS 0 AND AWAY FROM 0.
  • ROUND UP 240.6 TO THE NEAREST INTEGER.
USING CEILING.MATH 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

  • ROUNDING 25 TO THE NEAREST MULTIPLE OF 3
  • =CEILING.MATH(25,3)
  • In this function, 25 is the number to be rounded with the significance of 3. i.e. to the next nearest multiple of 3 which is 27. Hence the answer is 27.
  • ROUNDING -20 TO THE NEAREST MULTIPLE OF 6 TOWARDS 0 AND AWAY FROM 0.
  • TOWARDS 0
  • =CEILING.MATH(-20,6,0)
  • Here -20 is the number to be rounded, 6 is the significance and mode is 0 which will round it towards 0. Hence -18 is the answer.
  • AWAY FROM 0
  • =CEILING.MATH(-20,6,1)
  • The difference is in the mode which is 1 and take it away from the 0. Hence the answer is -24
  • ROUNDING UP 240.6 TO THE NEAREST INTEGER.
  • =CEILING.MATH(240.6)
  • Here 240.6 is the integer to be rounded up. So we omit both the options and it becomes  a simple roundup kind of function and rounds up to the next integer. If it were negative, it’ll go towards 0 i.e.-240

GENERALIZED STEPS TO USE CEILING.MATH FUNCTION

HERE ARE THE STEPS TO USE COUNT FUNCTION     PLACE YOUR CURSOR IN THE CELL WHERE YOU WANT THE RESULT.

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