1. Computing

Round Numbers Down in Excel

By

Excel's ROUNDDOWN Function
Round Numbers Down in Excel

Round Numbers Down in Excel

© Ted French

Round Numbers Down in Excel

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

The ROUNDDOWN 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 downward.

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

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

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

= ROUNDDOWN ( 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 Down 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 ROUNDDOWN function however, always leaves the rounding digit unchanged regardless of the value of the number to its right.

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

To show the effect the ROUNDDOWN 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.397
 E1  -  10
 

Entering the ROUNDDOWN Function

  1. Click on cell D2 to make it the active cell - this is where the results of the ROUNDDOWN 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 ROUNDDOWN 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.3 should appear in cell D2.

  11. When you click on cell D2 the complete function = ROUNDDOWN ( 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 273.97 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 273 should appear in cell F2.

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

©2014 About.com. All rights reserved.