Welcome, Excellers, to another Excel Formula tip in my #FormulaFriday series. Today let’s look at rounding numbers down in Excel using the ROUNDDOWN function. I want to give a quick disclaimer here. Once you round numbers in Excel (either up or down), you will lose precision in your calculations.
So, if precision is a concern in your calculations, I recommend reducing the number of decimal places in your cells by using the formatting options in Excel.
To make it clear, the ROUNDDOWN function in Excel will CHANGE your data and affect the results of your calculations.
The ROUNDDOWN Function Syntax.
Here is a refresher of the ROUNDDOWN function syntax.
=ROUNDOWN(number,num_digits)
Number – this refers to the value to be rounded. This actual data for rounding 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.
In the rules for rounding numbers that Excel follows, the number to the right of the rounding digit determines whether the rounding digit is 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.
An Excel ROUNDDOWN Example.
Click on cell D2 to make it the active cell – this is where the results of the Excel ROUNDDOWN function will be displayed.
- 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 27.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 is rounded down. Remember I said that Excel ROUNDOWN could affected your calculations? Let’s have a look at how this works.
You can see our original number in cell D1, and our rounded down number in D2. Both of the numbers then multiplied by 10. The original number result is significantly different. 353.97 in our original number whereas when we ROUNDOWN the number this is reduced to 353.00.
Do you want to see even more Excel tips? Why not bookmark my Formula Friday and Macro Monday Pages.
