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
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:
- 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
For help with this example, see the image above.
In this example we will use the CEILING function to reduce the number of decimal places of a value from four to one.
Entering the Data
- Enter the number 34.2217 into the designated cell D1
Entering the CEILING Function
- Click on cell E1 to make it the active cell - this is where the results of the CEILING function will be displayed
- Click on the Formulas tab of the ribbon menu
- 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
- Click on cell D1 in the worksheet to enter that cell reference into the dialog box
- In the dialog box, click on the Significance line
- Type in 0.10 - the number in D1 will be rounded up to the nearest multiple of 0.10
- Click OK.
- The answer 34.3 should appear in cell E1
- How Excel arrives at this answer is that:
- first it removes the three insignificant digits (217) from the end of the number
- next it rounds the remaining digit of the number up to 34.30 since this is the next highest multiple of 0.10 after 34.20
- When you click on cell E1 the complete function = CEILING ( D1 , 0.1 ) appears in the formula bar above the worksheet