When rounding in Excel, you often need to round numbers down - to whole numbers or several decimal places.
The ROUNDDOWN function is used to always round numbers "down". Its difference from ROUND is that regardless of the digit after the rounding position, the rounded part of the number always remains the same.
ROUNDDOWN function syntax
=ROUNDDOWN(Number, Num_digits)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | Num_digits | ROUNDDOWN | ROUND |
| 2 | 3.789 | 0 | 3=ROUNDDOWN(A2,B2) | 4=ROUND(A2,B2) |
| 3 | 3.789 | 1 | 3.7=ROUNDDOWN(A3,B3) | 3.8=ROUND(A3,B3) |
| 4 | -3.789 | 0 | -3=ROUNDDOWN(A4,B4) | -4=ROUND(A4,B4) |
| 5 | 1999 | -3 | 1000=ROUNDDOWN(A5,B5) | 2000=ROUND(A5,B5) |
| 6 | 12.3456 | 2 | 12.34=ROUNDDOWN(A6,B6) | 12.35=ROUND(A6,B6) |
ROUNDDOWN just cuts off the extra digits, moving toward zero: −3.789 becomes −3 (not −4). 1999 with −3 becomes 1000. For negative numbers, FLOOR.MATH or INT round toward minus infinity instead.
When the second argument is zero, rounding to whole numbers is performed. If the argument is made negative (-1, -2, -3...), the number is rounded to tens, hundreds, thousands, etc.
Example formula with the ROUNDDOWN function
The formula below rounds down the value of cell A1 to two decimal places.
=ROUNDDOWN(A1, 2)| A | B | |
|---|---|---|
| 1 | Value | To 2 decimals |
| 2 | 19.999 | 19.99=ROUNDDOWN(A2,2) |
| 3 | 4.505 | 4.5=ROUNDDOWN(A3,2) |
| 4 | 0.009 | 0=ROUNDDOWN(A4,2) |
| 5 | -2.678 | -2.67=ROUNDDOWN(A5,2) |
19.999 becomes 19.99 — handy when a price or a discount must not be rounded up. 0.009 becomes 0.
Round down procedure in Excel
There are often situations when you need to round many numbers in a table at once - an entire column or row. In this case, you need to actually perform the rounding procedure, not just change the number of visible digits.
Excel doesn't have this capability, so I decided to add it to my Excel add-in. You can either choose or enter yourself the number of digits before and after the decimal point. See how it works:
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.