HOW TO USE CEILING.MATH FUNCTION IN EXCEL ?

Table of Contents

• INTRODUCTION
• PURPOSE OF CEILING.MATH FUNCTION IN EXCEL
• PREREQUISITES TO LEARN CEILING.MATH FUNCTION
• WHAT IS THE SYNTAX OF CEILING.MATH FUNCTION IN EXCEL?
• EXAMPLE SHOWING THE USE OF CEILING.MATH FUNCTION IN EXCEL
  • DATA SAMPLE
  • STEPS TO USE CEILING.MATH FUNCTION-EXAMPLE
  • GENERALIZED STEPS TO USE CEILING.MATH FUNCTION
• FAQs
  • CEILING.MATH vs ROUNDUP FUNCTION

INTRODUCTION

In this article we’ll learn the ways to use Ceiling.Math function in Excel.

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?

WHAT IS THE SYNTAX OF CEILING.MATH FUNCTION IN EXCEL?

The Syntax for the CEILING.MATH 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 SHOWING THE USE OF CEILING.MATH FUNCTION IN EXCEL

DATA SAMPLE

Let us take 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

HERE ARE THE STEPS TO USE CEILING.MATH 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.
  
  

FAQs

CEILING.MATH vs ROUNDUP FUNCTION

Both of the functions, CEILING.MATH and ROUNDUP are associated with the operation of rounding with a very simple difference.

CEILING.MATH can be controlled to round to the next multiple of any specified number whereas ROUNDUP will simply round the number to the next whole number.

For example, the following table shows the outcome of various numbers when passed into the CEILING.MATH and ROUNDUP Functions.

NUMBER	CEILING.MATH FORMULA	RESULT	ROUNDUP FORMULA	RESULT
16.1	=CEILING.MATH(16.1,3,0)	18 -16.1 rounded to the next multiple of 3	=ROUNDUP(16.1,3)	16.1
16.0054	=CEILING.MATH(16.0054,3,1)	18- 16.0054 rounded to the next multiple of 3	=ROUNDUP(16.0054,3)	16.006 – 16.0054 rounded up to 3 decimal digits
17	=CEILING.MATH(17,5)	20 -17 rounded to the next multiple of 5	=ROUNDUP(17,3)	17 – no digits after decimal so 17 is the result.
14.5	=CEILING.MATH(14.5,3,1)	15-14.5 rounded to the next multiple of 3 i.e. 15.	=ROUNDUP(15,0)	15- 14.5 rounded to next integer as no parameter for digits after decimal given.