PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
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.
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.
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
- USE THE FUNCTION
- =CEILING.MATH(NUMBER, SIGNIFICANCE, MODE)
- Press ENTER and the result will appear.
OTHER WAYS TO REACH THIS ARTICLE
CEILING.MATH FUNCTION, CUSTOMIZED ROUNDING OF NUMBER, ROUND TO ANY NUMBER
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.