CEILING.PRECISE Function

What is the CEILING.PRECISE Function?

The CEILING.PRECISE function is categorized under Excel Math and Trigonometry functionsFunctionsList of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst. It will round up a number to the nearest integer or multiple of significance.

As a financial analystFinancial Analyst Job DescriptionThe financial analyst job description below gives a typical example of all the skills, education, and experience required to be hired for an analyst job at a bank, institution, or corporation. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models, we can use CEILING.PRECISE in setting the pricing after currency conversion, discounts, etc. When preparing financial models, it helps us round up the numbers per a requirement.

CEILING.PRECISE was introduced in MS Excel 2010 to replace the CEILING function. It was subsequently replaced by the CEILING.MATH function.

Formula

=CEILING.PRECISE(number, [significance])

The CEILING.PRECISE function uses the following arguments:

Number (required argument) – This is the value which we wish to round off.

Significance (optional argument) – This specifies the multiple of significance to round the supplied number to.

If we omit the argument, it takes default value 1. That is, it will round up to the nearest integer. The argument will ignore the arithmetic sign.

So, remember that, by default, the significance argument is 1.

The CEILING.PRECISE function uses the absolute value of the multiple, so that it returns the mathematical ceiling regardless of the sign of the number and significance.

How to use the CEILING.PRECISE Function in Excel?

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

Example 1

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

Number (argument)

Significance (argument)

Result

Remarks

110.67

111

As the [significance] argument is omitted, it takes on the default value of 1 and rounds off away from zero.

21

3

21

The function rounds up to the nearest multiple of 3. As 21 is a multiple of 3, we got the result as 21.

32.25

0.1

32.3

It rounded up away from 0.

-12.25

-1

-12

It rounds -12.25 down (away from 0) to the nearest integer that is a multiple of 1. The arithmetic sign of the [significance] argument is ignored by the function. So, if the value given was 12.25 and significance was -1, the function would be rounded up away from 0.

245

100

300

It rounded up to nearest multiple of 100 away from 0.

$4.67

1

5

It rounded up to nearest multiple of 5 away from 0.

The formula used and results in MS Excel are shown below:

Example 2

Suppose we wish to know how many cupcakes will fit in a rectangle box of different sizes. We are given the data below:

The item per container depicts the number of items, which is the cupcakes to be held in the rectangular box.

The formula we will use is =CEILING.PRECISE(A2,B2). It rounds up A2 to the nearest multiple of B2 (that is, items per box). The value derived will be divided by the number of containers. For example, in the second row, 100 will be rounded to a multiple of 24 and the result will be divided by 24.

We get the result below:

A few notes about the CEILING.PRECISE Function

#VALUE! error – Occurs when any of the arguments is non-numeric.

Instead of the CEILING.PRECISE function, we can use the INT function or CEILING.MATH function.

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 CFI resources:

Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial

Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career

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.