Microsoft Excel provides multiple formulas to round numbers. We have formulas like – ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD, and they all can be used to round numbers in excel.
Rounding is the process of removing the least significant digits from a number so that we are left with a simpler value that is very close to the original number.
In short, rounding gives you an approximate value that is quite accurate.
ROUND Function In Excel
The ROUND function is the most popular rounding function in excel. Round Function can round a number to the right or left of the decimal point.
Syntax
Here, 'number' represents the value that needs to be rounded. This is a required parameter. This can be any real number; it can also be a reference to a cell containing a number.
The 'num_digits' argument is also a required parameter. This signifies the number of digits to which you want to round the number argument. This can take a value that is positive, negative, or zero.
- If 'num_digits' is positive or greater than zero, the number is rounded to a specified number of decimal places.
- If 'num_digits' is negative or less than zero, the number is rounded to the left of the decimal point.
- If 'num_digits' is zero, the number is rounded to the nearest whole number or integer.
Note: If the number is already rounded to the given number of places, then no rounding occurs, and its value stays the same.
Now, let's see some examples of the ROUND function in excel and try to understand how to use ROUND function in excel.
Round to Right
In order to round the specified 'number' to the right of the decimal point, the 'num_digits' parameter should have a positive value.
Example 1: Use the Excel Round Function to round a number to 3 decimal places.
Consider rounding the number 5792.584345 (present in the cell A3) to 3 decimal places.
The formula would be as follows:
The result is 5792.584 since the number to the right of 4 is 3, so the rounding digit does not change.
Example 2: Use the Round Function in Excel to round a number to 2 decimal places.
Round the number 43.47894 (present in cell A4) to 2 decimal places.
The formula would be as follows:
The result is 43.479 since the number to the right of 8 is 9, so the rounding digit is increased by one.
Round to Left
In order to round the specified 'number' to the left of the decimal point, the 'num_digits' parameter should have a negative value.
Example 3: Use the Excel Round Function to round a number to the nearest 10.
We need to round the number 6593.578 (present in the cell A9) to the nearest 10.
The formula would be as follows:
'num_digits' parameter, in this case, is -1, which means rounding needs to be done to the nearest 10, and hence the result is 6590.
Example 4: Use the Round Function in Excel to round a number to the nearest 100.
Round the number 5689.56 (present in cell A10) to the nearest 100.
The formula would be as follows:
'num_digits' parameter, in this case, is -2, which means rounding needs to be done to the nearest 100, and hence the result is 5700.
Similarly, to round any number to the nearest 1000, the value of the 'num_digits' parameter should be -3 and so on.
Example 5: Use the Excel Round Function to round a number to the nearest whole number.
Round the number 68.394 (present in cell A11) to the nearest whole number.
The formula would be as follows:
'num_digits' parameter, in this case, is 0, which means rounding needs to be done to the nearest 1, and hence the result is 68.
Example 6: Use the Round Function in Excel to round a negative number to the nearest integer.
Round the number -58.69 (present in the cell A12) to the nearest integer.
The formula for this problem would be as follows:
Like Example 5, in this case, the 'num_digits' parameter is 0, which means that the rounding needs to be done to the nearest 1, and hence the result is -58.
Nesting Inside Excel Round Function
Other functions that return a numeric result can be nested within the ROUND function. This can help in using the ROUND function as part of a more complicated formula.
Example 7: Use Excel Round Function to round sum of two numbers
Round the result of the sum of two numbers 5869.548 and 5781.579 (present in cells A17and B17 respectively) to 1 decimal place.
The formula would be as follows:
The result of A17 +B17 = 11,651.127 and rounding it to 1 decimal place results in 11564.1.
Example 8: Use Round Function in Excel to round the division quotient of 2 numbers
Round the division quotient of 2 numbers 669.547 and 61.578 (present in cells A18 and B18, respectively) to the nearest integer.
The formula would be as follows:
The quotient A18/B18 = 10.8731527493585, and rounding it to the nearest one results in 11.
ROUNDDOWN Function In Excel
The ROUNDDOWN Function in Excel rounds down a given number to the specified number of decimal places.
The main difference between the ROUNDDOWN Function and ROUND Function is – In the ROUND function, the result depends on the rounding digit's value. If it is less than five, then the number is rounded down; else, it is rounded up.
However, in the case of the ROUNDDOWN Function, the specified number is always rounded downwards.
Syntax:
Here, 'number' represents the number that needs to be rounded. This is a required parameter. This can be any real number; it can also be a reference to a cell containing a number.
The 'num_digits' argument is also a required parameter. This signifies the number of digits to which you want to round the number argument. This can take on a value that is positive, negative, or zero.
Example 9: Use ROUNDDOWN Function in Excel to round a number down to 3 decimal places.
We want to round the number 589.125678 (present in cell A23) down to 3 decimal places.
The formula would be as follows:
And the result is 569.125. Even if the rounding digit is followed by a number greater than 5, the rounding number is retained.
Example 10: Use Excel ROUNDDOWN Function to round a number to the nearest integer.
Round a number 45710.265 (present in cell A24) down to the nearest integer.
The formula for this would be as follows:
Resulting in 45710.
Example 11: Use ROUNDDOWN Function in Excel to round a number down to the nearest 10.
Consider rounding the number 548.256 (present in cell A25) down to the nearest 10.
The formula would be as follows:
And the result would be 540.
Note that even though the rounding number 4 is followed by a number greater than 5, however, ROUNDDOWN Function does not consider this and simply rounds down the number to the nearest 10.
Example 12: Use Excel ROUNDDOWN Function to round a number down to the nearest 100.
In this example, we need to round the number 2579.457 (present in the cell A26) down to the nearest 100.
The formula for this would be as follows:
Resulting in 2500. Even though the rounding number 5 is followed by 7, the number gets rounded down to 2500.
Example 13:Â Use ROUNDDOWN Function in Excel to round a negative number down to 2 decimal places.
We have to round -576.5783 (present in cell A27) down to 2 decimal places.
The formula would be as follows:
Resulting in a value -576.57.
Example 14: Use Excel ROUNDDOWN Function to round a negative number down to the nearest integer.
So here we have to round -2576.54 (present in cell A28) down to the nearest integer.
The formula would be as follows:
Resulting in -2576.
In this case, ROUNDDOWN Function first finds the absolute value of the given number, which is 2576.54, then rounds it down to the nearest integer, which results in 2576. Finally, it affixes the negative sign, which gives -2576.
ROUNDUPÂ Function In Excel
The ROUNDUP function in Excel rounds up a given number to the specified number of decimal places. It is just opposite to the ROUNDDOWN Function.
The main difference between the ROUNDUP function and ROUND the Function is – In the ROUND function, the result depends on the rounding digit's value. If it is greater than or equal to 5, then the number is rounded up; else, it is rounded down.
However, in the case of the ROUNDUP function, the specified number is always rounded upwards.
Syntax:
Here, 'number' represents the number that needs to be rounded. This is a required parameter. This can be any real number; it can also be a reference to a cell containing a number.
The 'num_digits' argument is also a required parameter. This signifies the number of digits to which you want to round the number argument. This can take on a value that is positive, negative, or zero.
Example 15: Use ROUNDUP Function in Excel to round a number up to 3 decimal places.
We need to round 478.59786 (present in cell A33) up to 3 decimal places in this example.
The formula would be as follows:
Resulting in 478.598.
Here the rounding number 7 is increased by one, so it is replaced by the number 8 to give the result.
Example 16: Use Excel ROUNDUP Function to round a number up to 1 decimal place.
Consider rounding a number 58.5245 (present in cell A34) up to 1 decimal place.
The formula for this would be as follows:
And the result is 58.6.
Note that even though the rounding digit five is followed by 2, the value is not retained but increases by 1, resulting in 6 after the decimal point.
Example 17: Use ROUNDUP Function in Excel to round a number up to the nearest integer.
We need to round 576.35 (present in cell A35) up to the nearest integer.
The formula would be as follows:
Resulting in 577.
Example 18: Use Excel ROUNDUP Function to round a number up to the nearest 10.
We need to round a number 5862.12 (present in cell A36) up to the nearest 10.
The formula would be as follows:
And the result would be  5870.
In this example, rounding number 6 is rounded up to the nearest 10, thereby giving the result as 5870.
Example 19: Use ROUNDUP Function in Excel to round a number up to the nearest 100.
We need to round a number 5868.5757 (present in cell A37) up to the nearest 100.
The formula would be as follows:
And the result is 5900. The rounding digit 8 is rounded up to the nearest 100, thereby giving the result as 5900.
Example 20: Use Excel ROUNDUP Function to round a negative number to 1 decimal place.
We need to round a number -6485.56 (present in cell A38) up to 1 decimal place.
The formula would be as follows:
And the result is -6485.6, the rounding digit 5 is increased by 1.
Example 21: Use ROUNDUP Function in Excel to round a negative number up to the nearest integer.
We need to round a number -472.25 present in cell A39 up to the nearest integer.
The formula would be as follows:
And the result is -473.
In this case, the ROUNDUP function first takes the absolute value of the number, which is 472.25, and rounds it up to the nearest integer, 473, then affixes the negative sign for the final result -473.
MROUND Function In Excel
The MROUND Function is used for rounding a number to the nearest multiple. So, this Function will round a number up or down depending on the nearest multiple.
Syntax:
'number' signifies the number or the cell value that needs to be rounded.
'multiple' is the multiple you want to round your number to.
The last remaining digit can be rounded up (away from zero) or down (towards zero) depending on the remainder obtained by dividing the number by the multiple.
- Â Â Â Â Â Â Â If the value of the remainder is equal to or greater than half of the multiple, then the MROUND Function rounds up the last digit.
- Â Â Â Â Â Â Â If the value of the remainder is less than half the multiple, then the last digit is rounded down.
Let's understand this with the help of some examples:
Example 22: Use MROUND Function in Excel to round a number to the nearest multiple of 2
In this example, we need to round 583 (present in the cell A44) to the nearest multiple of 2.
The formula for this would be as follows:
Now let's try to find out the result – The nearest multiple of 2 can either be 584 or 582. Which one will it be?
If we divide the number (583) by the multiple (2), we get 291 as quotient and 1 as remainder. Since the remainder 1 is equal to half of the multiple (2) so, the number is rounded upwards to 584.
Example 23: Use Excel MROUND Function to round a number to the nearest multiple of 0.5
Let's say we need to round a number 583.58 (present in cell A45) to the nearest multiple of 0.5.
The formula would be as follows:
Now let's get to the result – The nearest multiple of 0.5 can either be 583.5 or 584.
If you divide 583.58 by 0.5, the quotient is 1167, and the remainder is 0.080, and since the remainder is less than half the multiple, so the final result is 583.5.
FLOOR Function In Excel
The Excel FLOOR function is used for rounding a number down to the nearest specific multiple. It is similar to the Excel MROUND function except that it always rounds the number downwards.
Syntax:
'number' signifies the number or the cell value that needs to be rounded.
'significance' is the multiple you want to round your number to.
Some rules for using the FLOOR function:
- If both the 'number' and 'significance' are greater than 0, then the number is rounded down toward zero.
- If the 'number' is positive, but 'significance' is negative, then the result will be an error #NUM.
- If the 'number' is negative and 'significance' is positive, then the value is rounded away from zero.
Note: If you are using Excel 2003 and 2007, both the 'number' and significance' must have the same sign, or else it will come out as an error. In later Excel versions, there is an improvement in the FLOOR function, and it can handle a negative number with a positive significance.
Example 24: Round a number down to the nearest multiple of 2.
Consider rounding a number 839 (present in cell A50) down to the nearest multiple of 2.
The formula would be as follows:
And the result would be 838 (not 840) since the number is rounded downwards.
Example 25: Round a number down to the nearest multiple of 0.5
We need to round 9763.9 (present in cell A51) down to the nearest multiple of 0.5.
The formula would be as follows:
And the result is 9763.5
Example 26: Round a negative number down to the nearest multiple of 3
Round the number -57894 (present in cell A52) to the nearest multiple of 3.
The formula would be as follows:
And the result is -57894 since the number is a multiple of 3; hence it is not rounded down.
Example 27: Round a number down to the nearest multiple of -2.
Round the number 167 (present in the cell A53) to the nearest multiple of -2.
The formula would be as follows:
Since the significance is negative and the number is positive, the result is #NUM error.
CEILING Function In Excel
The Excel CEILING function is used for rounding a number upwards to the nearest specific multiple. It is similar to the Excel MROUND function except that it always rounds the number upwards.
Syntax:
'number' signifies the number or the cell value that needs to be rounded.
'significance' is the multiple you want to round your number to.
Rounding with Excel CEILING function is similar to the FLOOR function except that the number is rounded up or away from zero instead of down.
Some rules for using the CEILING function:
- Â Â Â Â Â Â Â IF both arguments are positive, then the number is rounded up to the nearest multiple.
- Â Â Â Â Â Â Â If the 'number' is positive, and the 'significance' is negative, the CEILING function will result in an error #NUM.
- Â Â Â Â Â Â Â A negative 'number' with a positive 'significance' will also be rounded up.
- Â Â Â Â Â Â Â If both the 'number' and 'significance' are negative, then the number is rounded down.
Example 28: Round a number up to the nearest multiple of 5
We need to round the number 4753 (present in the cell A58) up to the nearest multiple of 5.
The formula would be as follows:
And the result is 4755 (not 4750) since the number is rounded up.
Example 29: Round a number up to the nearest multiple of 0.5
Consider rounding a number 589.29 (present in the cell A59) up to the nearest multiple of 0.5.
The formula would be as follows:
And the result is 589.5
Example 30: Round a negative number up to the nearest multiple of 6.
Let's say we need to round -3647 (present in the cell A60) up to the nearest multiple of 6.
The formula would be as follows:
And the result is -3642
Example 31: Round a number up to the nearest multiple of -5
Let's consider rounding a number 7348 (present in the cell A61) up to the nearest multiple of -5.
The formula for this would be as follows:
And the result is the #NUM error since the CEILING function results in an error #NUM error if the 'number' is positive, and the 'significance' is negative.
Example 32: Round a negative number to the nearest multiple of -2.
Round the number -6847 (present in the cell A62) to the nearest multiple of -2.
The formula would be as follows:
And the result is -6848
INT Function In Excel
The INT function is used to round down a number, with the result being an integer only. If a number is a decimal, then only the whole number is returned in the result.
Positive numbers are rounded down to zero, while negative numbers are rounded away from zero. Since the Function runs down, negative numbers become more negative.
This Function has only one argument, so it is the most straightforward Function to use out of all Excel's Round functions.
Syntax:
'number' signifies the number or the cell value that needs to be rounded.
Example 33: Round down a positive number
Consider rounding a number 578.25 (present in the cell A67) using the INT Function.
The formula would be as follows:
And the result is 578
Example 34: Round down a negative number
Round the number -475.47 (present in the cell A68) using the INT Function.
The formula would be as follows:
And the result is -476.
TRUNCÂ Function In Excel
TRUNC Function truncates a given number to a specified number of decimal places based on an optional argument. It does not round numbers but simply truncates as specified.
Syntax:
'number' signifies the number or the cell value that needs to be truncated.
'num_digits' is an optional parameter which signifies the precision for truncation.
Some rules for using the TRUNC Function:
- Â Â Â Â Â Â Â If the 'num_digits' argument is not specified, then the number is rounded to an integer.
- Â Â Â Â Â Â Â If the 'num_digits' argument is positive, then it truncates the specified number of digits to the right of the decimal point.
- Â Â Â Â Â Â Â If the 'num_digits' argument is negative, then it truncates the specified number of digits to the left of the decimal point.
TRUNC V/S INT
TRUNC and INT function are very similar because both can return the integer part of a number (when TRUNC is used without the optional argument).
However, from a logical point of view, the difference between the two functions is that – TRUNC simply truncates the decimal places from a number. At the same time, INT rounds the number down to an integer. So for positive numbers, the behavior of both TRUNC (without optional argument) and INT is the same.
However, with negative numbers, the results can be different. For instance – INT(-5.4) returns -6 because INT rounds down to the lower integer whereas TRUNC(-5.4) returns -5.
Example 35: Truncate a number 2 decimal places to the right
Truncate a number 879.5758 (present in the cell A73) to 2 decimal places to the right.
The formula would be as follows:
And the result is 879.57
Example 36: Truncate a negative number 1 decimal to the left.
Truncate a number -257.68 (present in the cell A74) to 1 decimal to the left.
The formula would be as follows:
And the result is -250.
Example 37: Truncate the number to the nearest integer.
Truncate a number 6827.25 (present in the cell A75) to the nearest integer.
The formula would be as follows:
or
And the result is 6827.
Even Function In Excel
Microsoft Even function rounds a given positive odd number up to the next even number. If the given number is odd and negative, then the number is rounded downwards (away from zero).
However, the given number is already an even number, so the number is retained as is.
Syntax:
'number' signifies the number or the cell value that needs to be rounded.
Example 38: Round up an odd positive number
Consider rounding a number 879.54 (present in the cell A80) using the EVEN Function.
The formula would be as follows:
And the result is 880 (rounded up)
Example 39: Round up an odd negative number
Consider rounding a number -2157.68 (present in the cell A81) using the EVEN Function.
The formula would be
And the result is -2158 (rounded down)
Example 40: Round up an even positive number
Consider rounding a number 414 (present in the cell A82) using the EVEN Function.
The formula would be as follows:
And the result is 414 (since it is already an even number, so it remains the same)
Odd Function In Excel
Microsoft ODD function rounds a given positive even number up to the next odd number. If the given number is even and negative, then the number is rounded downwards (away from zero).
However, the given number is already odd, so the number is retained as is.
Syntax:
'number' signifies the number or the cell value that needs to be rounded.
Example 41: Round up an even positive number
Consider rounding a number 879.54 (present in the cell A87) using the ODD Function.
The formula would be as follows:
And the result is 881 (rounded up)
Example 42: Round up an even negative number
Consider rounding a number -2157.68 (present in the cell A88) using the ODD Function.
The formula would be as follows:
And the result is -2159 (rounded down)
Example 43: Round up an odd positive number
Consider rounding a number 513 (present in the cell A89) using the ODD Function.
The formula would be as follows:
And the result is 513 (since it is already an odd number, so it remains the same)
So, these were all the Rounding Functions in Excel. Hope you would find them useful 🙂
About Saurav Ahuja