The ROUNDDOWN function will round any number down to a specified number of digits. This function differentiates itself from the standard ROUND function by always rounding values down.
Contents:
Syntax
= ROUNDDOWN(number, num_digits)number = the number you wish to round down
num_digits = a value specifying the precision of the rounding
Explanation
The ROUNDDOWN function is part of the "Math and Trigonometry" group of functions within Excel.
This function will round any number, positive or negative, down to a specified digits place by changing the num_digits argument. The default value of 0, will round down to the nearest integer and everything after the decimal point will be rounded.
To round down to different digits place, increase the num_digits argument. A value of 1, would round after 1 decimal place (2.856 becomes 2.8). A value of 2 would round after 2 decimal places (2.85) and so on.
A negative value can even be used to round numbers before the decimal point. For example, a num_digits value of -2 would round 7,456.5 to 7,400. Essentially rounding downwards to the specified tens/hundreds/thousands... etc. place.
Notes:
- If the either input is non-numerical, a #VALUE! error will be returned
- A blank cell will be returned as a 0
Different Methods of Rounding in Excel
There are a lot of different ways to round numbers, and it all depends on the end goal you're trying to reach. Here are a few different rounding functions and when you would want to use them:
ROUND - normal rounding, rounds a specified place
MROUND - rounds to the nearest specified multiple
ROUNDUP - rounds up away from 0, to the nearest specified place
CEILING.MATH - rounds up to the nearest integer or significant multiple
ROUNDDOWN -rounds down away from 0, to the nearest specified place
FLOOR.MATH - rounds down to the nearest integer or significant multiple
INT - rounds down to the nearest integer, returns an integer
TRUNC - truncates all decimal places to specified place
Examples
1. How to Round a Number Down (To the Nearest Integer)
To round a number down to the nearest integer, we can use the ROUNDDOWN function with any input number (either a cell reference or hard coded value) and combine that with a 0 for the num_digits argument.
By using a 0 for the num_digits argument, we are telling the formula that we want to round the trailing decimals down to the nearest whole number.
This will automatically round any number with a decimal or fractional portion to the nearest integer.
= ROUNDDOWN(number, 0)
2. How to Round a Number Down to A Specific Number of Digits
The ROUNDDOWN function can also be used to round any number down to a specific number of significant digits.
As you increment the num_digits argument upwards from zero, the place at which the round occurs moves right of the decimal point.
For example, a 0 in the num_digits input would round off the decimals, while 1 would round to the tenths place, 2 the hundredths place, and so on.
Negative numbers here can also be used to round down to places left of the decimal point, as shown in the next example.
= ROUNDDOWN(number, rounding_place)3. How to Round a Number Down to the Nearest Ones, Tens, Hundreds, Thousands Place, or Greater
By subbing in negative values for the num_digits argument, the ROUNDDOWN function can also be used to round to places left of the decimal point.
This can be used to round any number down to the nearest thousand, ten thousand, million, or any combination needed.
This is especially helpful when dealing with larger, less accurate numbers, where you want to round off extraneous numbers.
= ROUNDDOWN(number, negative_rounding_place)
4. How to Round a Time Down to the Nearest Hour, Half Hour, or Quarter Hour
The ROUNDDOWN function can also be used to round any given time down to the nearest quarter hour, half hour, or full hour.
These formulas work by taking an input time and multiplying it by the number of times that segment of time occurs in a day.
Because Excel sees time as a value between 0 and 1 (0 being 12 AM and 1 being 12PM) we need to convert the "Excel time" to regular time by multiplying by our segment value and performing our rounding on that value, before finally converting it back to Excel time by dividing it by the same segment value.
For example, in a day there are 24 hours, by multiplying the time by 24, rounding to the nearest integer, then dividing by 24 again, we convert the Excel time value into a form that we can round, then convert it back into a format that Excel reads as time.
Because there are 48 half hours in the day and 96 quarter hours in a day, these numbers give us the values that we need to multiple and divide by within our formulas.
Formula to round down to the nearest quarter hour:
= ROUNDDOWN(time * 96, 0) / 96Formula to round down to the nearest half hour:
= ROUNDDOWN(time * 48, 0) / 48Formula to round down to the nearest full hour:
= ROUNDDOWN(time * 24, 0) / 24