Excel: ROUNDDOWN – an explanation of this handy function
IONOS editorial team5 mins
Microsoft’s spreadsheet application provides users with numerous formulas to make working with values less complex. In addition to extensive formulas for algebra and statistics, the tool also provides a relatively simple function to round down values. While the ROUNDDOWN function may be simple, it’s a valuable tool for the everyday use of Excel. We will explain how to use the formula correctly.
Why would you need to round down in Excel?
Complex calculations can produce values with a large number of decimal places. However, most people will rarely need to work with such numbers. Whether finances, percentages or measurements: after two decimal places, the digits are usually less important. Numbers with many decimal places can look messy. Therefore, it’s important to prune back the number of decimal places to a useful length, i.e. round them down.
There are numerous ways to do this in Excel. For example, many users are familiar with the option to format cells so that only a predefined number of decimal places will appear in the result cell. However, this only adjusts how the value is displayed. The original number still exists in the background in Excel. This can have its advantages: if the value is used for additional calculations, the full correct number will be used. On the other hand, this is not always what you want. If you want to adjust the actual value, you will need to do so with a function like ROUNDDOWN which allows you to adjust a number to your preferred number of decimal places.
You can also use the ROUND function. This function provides additional options for rounding in Excel. ROUNDDOWN is simpler in design and thus easier to use if you just want to round down.
ROUNDDOWN function syntax
The function only requires two parameters. You must specify the number to be rounded down and the number of digits you want to round the number down to.
=ROUNDDOWN(number;num_digits)- Number: A real number that is either directly in the formula or specified by a cell reference.
- Num_digits: The number of digits to which you want to round the number. Positive parameters specify places to the right of the decimal point, while negative parameters specify places to the left of it. You can also enter cell references here.
In Excel, you can round whole numbers and round numbers to a specific decimal place. If you use a number greater than zero for the number of digits (i.e. num_digits), you are specifying how many decimal places the value should have. If you enter zero, the value will have no places to the right of the decimal point. Entering a negative number will round the number down to the nearest power of ten which is the number of decimal places to the left of the decimal point.
ROUNDDOWN always rounds down (not up) no matter what digit is in the relevant decimal place. This means that 4.9 would be rounded to 4.
Example: How to use the Excel ROUNDDOWN function
The easiest way to use the ROUNDDOWN function in Excel is to enter the values directly in the formula. Let’s look at an example of this:
=ROUNDDOWN(1234.5678;2) = 1234.56
=ROUNDDOWN(1234.5678;0) = 1234
=ROUNDDOWN(1234.5678;-2) = 1200
However, you can also obtain the number to be rounded down from another cell. The formula can thus be adapted for a large number of cells.
=ROUNDDOWN(A1;2)
If you apply this formula to other cells, you can round down multiple values at once. You can then integrate the resulting values into another calculation or directly nest the formulas in one another. This means that you can either add the ROUNDDOWN function to another formula or integrate the calculation itself into the formula for rounding down.
=ROMAN(ROUNDDOWN(1234.5678;0))
This example formula first rounds down the entered number to a whole number and then converts it into roman numerals: MCCXXXIV.
=ROUNDDOWN(QUOTIENT(A1;B1);-2)
In this example, we combined the ROUNDDOWN function with the QUOTIENT function. Excel divides the value in the first cell by the value in the second cell and then rounds the result down to the nearest hundred.
If you try to round down a number which already has fewer decimal places than you specified in the formula, nothing will happen. The missing decimal places will not be filled with zeros. For example, if you try to round down the number 5 to two decimal places, the result will still be 5. If you want to obtain 5.00, you will need to format the cell accordingly.
