The CEILING Function in Excel returns a given number rounded up to a specified multiple. CEILING Function is always used for rounding up. Its purpose is to Round a number up to nearest multiple. It returns a rounded number.

  • What is a CEILING Function in Excel?
  • What is the syntax for CEILING Function?
  • Where can be CEILING Function used?
  • What is the difference between CEILING Function and CEILING.MATH Function?

➡ What is a Ceiling Function in Excel?

The CEILING function in Excel rounds up a number to a given multiple. CEILING function can be used to set the pricing after currency conversion, discounts, etc. It works similar to the MROUND function, but the only difference is that CEILING always rounds up. CEILING function is a sub-category of the Compatibility Functions in Excel.

➡ What is the Syntax for Ceiling Function?

The syntax for CEILING function is very simple and easy to understand.

=CEILING(number,significance)

Where,

            Number – The number that should be rounded

            Significance – The multiple to use when rounding

The formula for CEILING Function can be written as

=CEILING(A1,1)  It will round the value in cell A1 upto the number 1.

Where can be Ceiling Function used?

CEILING Function can be used to round the time in hours, minutes or seconds upto their nearest interval. Time is a very important measure and used everywhere. Therefore, CEILING Function plays a very important role as it is used for rounding time. Likewise, it is also used to roundup the dates. Also, other applications of CEILING Function are to round up to the bundle sizes, randomly assign people to groups or teams of a specific size etc.

Example:

Let’s take a simple example to understand the working of CEILING Function. As shown in below picture we have positive and negative numbers in the data table. We have also given a significance for rounding the numbers.

Here, the first number is 50 and the significance is 5. But we all know that 50 is the exact multiple of 5. That’s why we get the output as 50.

The next number is 68 and the significance is 3. The output we get here is 69, because 69 is the multiple of 3 which is nearest to 68.

In this way, CEILING Function helps in rounding the numbers.

Ceiling Function in Excel

➡ What is the difference between Ceiling Function and Ceiling.Math Function?

The difference between CEILING Function and CEILING.MATH Function is as follows:

  • Unlike the CEILING.MATH Function, the CEILING Function does not provide a default multiple of 1 and -1 for positive numbers and negative numbers
  • It provides more control for rounding negative numbers than CEILING Function
  • MATH Function uses the absolute value of the significance argument. Altering the sign of significance does not affect the result
  • CEILING Function falls under the category of Compatibility functions whereas CEILING.MATH Function falls under the category of Math & Trig Functions

Recommended Blog:

➡ Top 3 Points to Remember:

  • Rounding does not occur in CEILING Function if the number is an exact multiple of significance
  • If number and significance are both negative in CEILING Function, it returns a value that will round down, away from zero
  • When the number is negative and significance is positive in CEILING Function, the value will round up towards zero

➡ Conclusion

Here, we come to know about the function. Its purpose, its uses, the formula to be use, where can we find this function. Hope your concept regarding the CEILING Function is clear and you can now carry out this function easily.

Leave a Reply

Related Tutorials

Connect to Data in Power Query
February 21, 2019
How to use Text to Column in Excel with 4 Difference Example
February 21, 2019
Things you should learn in Excel Course
February 13, 2019
How to use Clean Function in Excel
February 11, 2019
How to use the Excel Choose Function
February 11, 2019
How to use the Excel Code Function
February 11, 2019
How to use the Excel Column Function
February 11, 2019
How to use Char Function in Excel
January 28, 2019
Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
How to use AVERAGEIFS FUNCTION in Excel
December 10, 2018
How to use AVERAGEIF FUNCTION
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas and Functions in PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018