Excel has a lot of in built functions. Today let's look at the ROUNDOWN formula or function in Excel. One thing to be aware of before you start to round numbers up or down, you do lose precision in your data. If this is a concern I would recommend just reducing the number of decimal places in your cells by using the formatting options in Excel.
Firdtly let's take a look at the syntax of this formula-
= ROUNDOWN (NUMBER, NUM DIGITS)
Number – this reflects to the value you want to be rounded. This can be a number or it can be a cell reference.
Num_digits – this reflects to the number of decimal places to reduce the specified number to.
Normally Excel uses the number to the right of the rounding digit to determine whether the number is to be rounded up or down- the rules are as follows-
- 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.
But, with the ROUNDDOWN function Excel always leaves the rounding digit unchanged regardless of the value of the number to its right. Let's work through an example to make this clear.
Type 35.397 in Cell D1 on an Excel sheet. Click on cell D2 to make it the active cell – this is where the results of the ROUNDDOWN function will be displayed.
Now to write the formula or function
- Formulas tab
- Choose Math & Trig from the ribbon to open the function drop down list.
- Click on ROUNDDOWN in the list to bring up the 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.
- Click on the Num_digits line.
- Type a one "1" to reduce the value in D1 to one decimal place.
- Click OK.
- The answer 35.3 should appear in cell D2.
- When you click on cell D2 the complete function = ROUNDDOWN (D1, 1) appears in the formula bar above the worksheet
- That is it, your number has been rounded down.
ROUNDOWN can affect your calculations. Let's see this in action in the following example.
You can see our original number in cell D1, and our rounded down number in D2. Multiple both of these numbers by 10.
When both of the numbers then multiplied by 10, the original number result is significantly different. It is 353.97, whereas when using the ROUNDOWN function the result of the calculation is reduced to 353.00.
Whilst this is a useful function in Excel its limitations and implications for affecting calculations need to be taken into account.