Excel: ROUNDDOWN – an explanation of this handy function
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.
Related articles
Rawpixel.comShutterstock How to use the Excel COUNT function
Analyses and calculations in Excel can be highly complex. Excel’s worksheets let you create huge tables, but sometimes you just want to answer a very simple question: How many cells in the table contain a number? In big worksheets, it can be virtually impossible to check the whole thing manually. The COUNT function in Excel was created specifically to solve this problem.
Rawpixel.comShutterstock Excel's WORKDAY function explained
Many people use dates regularly in Microsoft Excel. However, the problem with dates is that they don’t automatically distinguish between working days and weekends. Excel does, however, provide a function that allows you to analyse working days separately. Using Excel’s WORKDAY function, you can calculate deadlines more precisely.
ChinnapongShutterstock How to use INDEX in Excel
Analysing huge quantities of data can be a frustrating task. However, Microsoft’s spreadsheet program offers a range of functions that make preparing worksheets easier and more efficient. For instance, the INDEX function in Excel enables you to replicate cell content. This can be very useful, particularly in combination with other functions.
How to use the Excel INDIRECT function
In Excel, cell references are used quite often. Rather than entering values into formulas individually, we pull them from the cells of tables. The INDIRECT Excel function lets you even obtain references from other cells. This can be helpful when working with multiple worksheets or when creating complex formulas. We explain how to use INDIRECT in Excel.
Excel ROUNDUP function: How to use it correctly
The Excel ROUNDUP function for figures is one of the basic functions of many commercial, scientific and financial applications. It is used to make calculation results easier to read where they have numerous decimal places. If you want to automatically round up your results, you can use the Excel ROUNDUP function.
