Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

CEILING Function

Returns a number rounded up to a supplied number that is away from zero to the nearest multiple of a given number

What is the Excel CEILING Function?

The Excel CEILING function is categorized under Math and Trigonometry functions. The function will return a number that is rounded up to a supplied number that is away from zero to the nearest multiple of a given number. MS Excel 2016 handles both positive and negative arguments.

The function is very useful in financial analysis as it helps in setting the price after currency conversion, discounts, etc. When preparing financial models, CEILING helps us round up the numbers as per the requirement. For example, if we provide =CEILING(10,5), the function will round off to the nearest 5 dollars.

 

Formula

=CEILING(number, significance)

 

The function uses the following arguments:

  1. Number (required argument) – It is the value that we wish to round off.
  2. Significance (required argument) – It is the multiple that we wish to round up. It would use the same arithmetic sign (positive or negative) as per the provided number argument.

 

How to use CEILING Function in Excel?

To understand the uses of the CEILING function, let’s consider a few examples:

 

Example 1 – Excel Ceiling

Let’s see the results from using the function when we provide the following data:

 

CEILING Function

 

The formula used and results in Excel are shown in the screenshot below:

 

CEILING Function - Example 1

 

Example 2 – Using CEILING with other functions

We can use CEILING to highlight rows in a group of data. We will use a combination of ROW, CEILING, and ISEVEN functions. Suppose we are given the data below:

 

Excel CEILING Function - Example 2

 

If we wish to highlight rows in groups of 3, we can use the formula =ISEVEN(CEILING(ROW()-4,3)/3) in conditional formatting.

 

CEILING Function - Example 2a

 

We will get the result below:

 

CEILING Function - Example 2b

 

In applying the function, remember to select the data before clicking on conditional formatting.

First, we “normalized” the row numbers to begin with 1 using the ROW function and an offset. Here 3 is n (the number of rows to group) and 4 is an offset to normalize the first row to 1 as the first row of data is in row 6. The CEILING function rounds incoming values up to a given multiple of n. Essentially, the CEILING function counts by a given multiple of n.

 

CEILING Function - Example 2c

 

The count shown above in column G is then divided by n that is 3. Lastly, the ISEVEN function is used to force a TRUE result for all even row groups, which triggers the conditional formatting.

 

Notes about the Excel CEILING Function

  1. #NUM! error – Occurs when:
    • If we are using MS Excel 2007 or earlier versions, provide the significance argument with a different arithmetic sign from the given number argument.
    • If we are using MS Excel 2010 or 2013 if the given number is positive and the supplied significance is negative.
  2. #DIV/0! error – Occurs when the significance argument provided is 0.
  3. #VALUE! error – Occurs when any of the arguments is non-numeric.
  4. CEILING is like MROUND but it always rounds up away from zero.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!