The ROUND function will round any number to a specified number of digits. There are plenty of real world situations in which you wouldn't want to use exact numbers, and rounding gives that control.
Contents:
Syntax
= ROUND(number, num_digits)number = the number you wish to round
num_digits = a value specifying the precision of the rounding
Explanation
The ROUND function is part of the "Math and Trigonometry" group of functions within Excel.
This function will round any number, positive or negative, to a specified digits place by changing the num_digits argument. The default value of 0, will round to the nearest integer and everything after the decimal point will be rounded.
To round to different digits place, increase the num_digits argument. A value of 1, would round after 1 decimal place (2.856 becomes 2.9). A value of 2 would round after 2 decimal places (2.86) 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,500. Essentially rounding 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 (Nearest Integer)
To round a number to the nearest integer, we can use the ROUND 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 up or down to the nearest whole number.
This will automatically round any number with a decimal or fractional portion to the nearest integer.
= ROUND(number, 0)
2. How to Round a Number to A Specific Number of Digits
The ROUND function can also be used to round any number 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 to places left of the decimal point, as shown in the next example.
= ROUND(number, rounding_place)
3. How to Round a Number to the Nearest Ones, Tens, Hundreds, Thousands Place, or Greater
By subbing in negative values for the num_digits argument, the ROUND function can also be used to round to places left of the decimal point.
This can be used to round any number 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.
= ROUND(number, negative_rounding_place)
4. How to Round a Time to the Nearest Hour, Half Hour, or Quarter Hour
The ROUND function can also be used to round any given time to the nearest quarter hour, half hour, or full hour.
These formulas work by taking any 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 converting it back to Excel time by dividing it by the same 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 to the nearest quarter hour:
= ROUND(time * 96, 0) / 96Formula to round to the nearest half hour:
= ROUND(time * 48, 0) / 48Formula to round to the nearest full hour:
= ROUND(time * 24, 0) / 24