1. Computing

Round Numbers Up in Excel

By

Excel's ROUNDUP Function
Round Numbers Up in Excel

Round Numbers Up in Excel

© Ted French

Round Numbers Up in Excel

Rounding numbers in Excel can be accomplished using a number of different functions depending on the results required.

The ROUNDUP function can be used to reduce a value by a specific number of decimal places. As its name suggests, it will always round the last digit up to the next highest number.

Unlike formatting options that allow you change the number of decimal places displayed, the ROUNDUP function actual alters the data in your worksheet.

Using this function can, therefore, affect the results of calculations.

The ROUNDUP 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 ROUNDUP function is:

= ROUNDUP ( Number, Num_digits )

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.

Num_digits - the number of decimal places to reduce the above number to.

Example: Round Numbers Up in Excel

For help with this example, see the image above.

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 ROUNDUP function however, always rounds the rounding digit up to the next number regardless of the value of the number to its right.

In this example we will use the ROUNDUP function to reduce the number of decimal places of a value from four to one.

To show the effect the ROUNDUP function can have on calculations, both the original number and the rounded one will be multiplied by 10 and the results compared.

Entering the Data

Enter the following data into the designated cells.
 
Cell    Data 
 D1  -  27.6312
 E1  -  10
 

Entering the ROUNDUP Function

  1. Click on cell D2 to make it the active cell - this is where the results of the ROUNDUP function will be displayed.

  2. Click on the Formulas tab of the ribbon menu.

  3. Choose Math & Trig from the ribbon to open the function drop down list.

  4. Click on ROUNDUP in the list to bring up the function's dialog box.

  5. In the dialog box, click on the Number line.

  6. Click on cell D1 in the worksheet to enter that cell reference into the dialog box.

  7. Click on the Num_digits line.

  8. Type a one " 1 " to reduce the value in D1 to one decimal place.

  9. Click OK.

  10. The answer 27.7 should appear in cell D2.

  11. When you click on cell D2 the complete function = ROUNDUP ( D1 , 1 ) appears in the formula bar above the worksheet.

Using the Rounded Number in Calculations

  1. Click on cell F1 to make it the active cell - this is where the multiplication formula will be entered.

  2. Type an equal sign to begin the formula.

  3. Click on cell D1 to enter that cell reference into the formula.

  4. Type an asterisk ( * ) - the symbol for multiplication in Excel.

  5. Click on cell E1 to enter that cell reference into the formula.

  6. Press the Enter key on the keyboard to complete the formula.

  7. The answer 276.312 should appear in cell F1.

  8. Type the number 10 in cell E2.

  9. Click on cell F1 to make it the active cell.

  10. Copy the formula in F1 to cell F2 using the Fill Handle or Copy and Paste.

  11. The answer 277 should appear in cell F2.

The different formula results in cells F1 and F2 - 276.312 vs. 277 show the effect rounding numbers can have on calculations.

©2014 About.com. All rights reserved.