Excel Ceiling Function Tutorial

Excel Ceiling Function Tutorial

Excel CEILING Function

Send to a Friend via Email

Recipient’s Email

This field is required.

Separate multiple addresses with commas. Limited to 10 recipients. We will not share any of the email addresses on this form with third parties.

Excel CEILING Function

Rounding Numbers in Excel

There are a number of different functions that can be used for rounding numbers in Excel.

Many of these functions have very specific jobs so the one you use depends on the results you want.

One such rounding function — the CEILING function — can be used to round a number upwards to the nearest multiple of a specified value.

What this means is that the function can be used to eliminate insignificant digits by rounding the data upwards to the nearest value that is considered significant.

For example, the function can be used to round up the cost of items to the nearest dime ( $ 0.10 ) to avoid having to deal with smaller change such as pennies ($ 0.01 ) and nickels ($ 0.05 ).

Changing Data with the Rounding Functions

Like other rounding functions, the CEILING function actual alters the data in your worksheet and will, therefore affect the results of any calculations that use the rounded values.

There are, on the other hand, formatting options in Excel that allow you change the number of decimal places displayed by your data without changing the numbers themselves.

Making formatting changes to data has no effect on calculations.

The CEILING Function’s Syntax and Arguments

A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments .

The syntax for the CEILING function is:

= CEILING ( Number, Significance )

Number — the value to be rounded. This argument can contain the actual data for rounding or it can be a cell reference to the location of the data in the worksheet.

Significance — the number of digits in this argument indicates the number of significant digits after the decimal point that will be present in the answer. In addition, the function rounds the Number specified above up to the nearest multiple of this value.

Excel’s Rules for Rounding

In the rules for rounding numbers that Excel follows, normally the number to the right of the rounding digit determines whether the rounding digit will be rounded up or down:

    Excel Ceiling Function Tutorial
  • If the value of the number to the right of the rounding digit is less than five, the rounding digit is left unchanged
  • If the value of the number to the right of the rounding digit is five or higher, the rounding digit is raised by one

The CEILING function however, always rounds the rounding digit up to the level of significance specified in the function no matter the value of the number to the right of the rounding digit.

Example: Rounding Numbers with Excel’s CEILING Function

As can be seen in the image above, this example will use the CEILING function to reduce the number of decimal places of a value from four to one.

Entering the Data

  1. Enter the number 34.2217 into the designated cell D1

Entering the CEILING Function

  1. Click on cell E1 to make it the active cell — this is where the results of the CEILING function will be displayed
  • Choose Math & Trig from the ribbon to open the function drop down list
  • Click on CEILING in the list to bring up the function’s dialog box
  • In the dialog box, click on the Number line

  • Leave a Reply