1. Computing

Rounding Numbers in Excel

By

Excel's ROUND Function
Rounding Numbers in Excel

Rounding Numbers in Excel

© Ted French

Rounding Numbers in Excel

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

For example, the ROUND function can be used to reduce a value by a specific number of decimal places.

Unlike formatting options that allow you change the number of decimal places displayed, Excel's rounding functions actual alters the data in your worksheet.

Using these functions can, therefore, affect the results of calculations.

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

= ROUND ( 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: Rounding Numbers 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

In this example we will use the ROUND function to reduce the number of decimal places of a value to zero.

To show the effect rounding numbers has 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  -  25.255
 E1  -  10
 

Entering the ROUND Function

  1. Click on cell D2 to make it the active cell - this is where the results of the ROUND 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 ROUND 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 zero " 0 " to reduce the value in D1 to zero decimal places.

  9. Click OK.

  10. The answer 25 should appear in cell D2.

  11. When you click on cell D2 the complete function = ROUND ( D1 , 0 ) 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 252.55 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 250 should appear in cell F2.

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

©2014 About.com. All rights reserved.