Buzz
Below article guides you on how to round numbers in Excel, making calculations faster.
Depending on how you want to round digits before and after the decimal point, there are many rounding functions in Excel.
1. Rounding Numbers Using the ROUND() Function
- Purpose: Rounding to the nearest number based on the specified number of digits
- Syntax: ROUND(number, num_digits)
Where:
+ number represents the value to be rounded.
+ num_digits represents the value to round. If:
num_digits = 0 => round to the nearest integer
num_digits > 0 => round to the corresponding decimal places according to the value of num_digits
num_digits = 0 => round to the corresponding integer places according to the value of num_digits
- Example: Perform rounding on the numbers in the table below using the round function
In the cell where you want the result, enter the formula: =ROUND(D7,E7)
Press Enter -> Copy the formula for the remaining values to get the result:
2. Rounding Numbers Using the MROUND() Function
- Purpose: Rounding to the nearest multiple of a specified number
- Syntax: MROUND(number, multiple)
Where:
+ number represents the value to be rounded
+ multiple: Number to determine the rounding multiple
- Note:
+ The MROUND function rounds away from zero if the remainder after dividing number by multiple is greater than or equal to half the value of multiple
+ The arguments must have the same sign; otherwise, it returns the error value #NUM
- Example: Round the following values using the MROUND() function
In the cell where you want the result, enter the formula: =MROUND(D7, E7)
Press Enter -> Copy the formula for the remaining values to get the result:
3. Round Numbers Using the ROUNDDOWN() and ROUNDUP() Functions
- Meaning: Differentiating between two similar functions:
+ The ROUNDDOWN() function always rounds a number towards zero
+ The ROUNDUP() function always rounds a number away from zero
- Syntax:
ROUNDDOWN(number, num_digits)
ROUNDUP(number, num_digits)
- number represents the value to be rounded
- num_digits: the number of digits to round to
- num_digits: the number of digits to round. If:
num_digits = 0 => round to the nearest integer
num_digits > 0 => round to the corresponding decimal places according to the value of num_digits
num_digits = 0 => round to the corresponding integer places according to the value of num_digits
- Example:
4. Rounding numbers using the CEILING() and FLOOR() functions
4.1 The CEILING function
Description: The function rounds up, away from zero, to the nearest significant multiple.
Syntax: CEILING(number, significance).
Where:
- number: The value to be rounded, a required parameter.
- significance: The multiple to round to, a required parameter.
Note:
- If the arguments in the function are not numbers -> the function returns the error value #VALUE!
- Rounded-up values are adjusted away from zero regardless of the sign of the number. If the number is an exact multiple of the significance, it is not rounded.
- If the number is negative and the significance is negative, the value is rounded down away from zero.
- If the number is negative and the significance is positive, the value is rounded up towards zero.
Example:
Round the values in the table below using the multiples provided:
In the cell where you want the result, enter the formula: =CEILING(C8, D8)
Press Enter and copy the formula for the remaining values to get the result:
4.2 The FLOOR Function
The Floor function, similar to the Ceiling function, rounds numbers down towards zero.
5. Rounding Numbers Using the EVEN() and ODD() Functions
- The Even() function rounds a number up to the nearest even integer
Syntax: EVEN(number)
Where: number is the value to be rounded
- The Odd() function rounds a number up to the nearest odd integer
Syntax: ODD(number)
Where: number is the value to be rounded
Example: Round the following values using the Even() and Odd() functions
In the cell where you want to calculate the rounded value using the Even function, enter the formula: =EVEN(B7)
Press Enter and copy the formula to get the results for the remaining values:
Similarly, in the cell where you want to calculate the rounded value using the Odd() function, enter the formula: =ODD(B7)
Press Enter and copy the formula to get the results for the remaining values:
6. Round numbers using the INT() and TRUNC() functions
- The INT function rounds the number down to the nearest integer.
Syntax: INT(number)
Where: number is the value to be rounded.
- The TRUNC function rounds the number to a specified number of decimal places.
Syntax: TRUNC(number, [num_digits])
Where:
- number is the value to be rounded.
- num_digits: is the number of decimal places to round to, an optional parameter with a default value of 0.
For example:
In the cell where you want to round using the Int function, enter the formula: = INT(B7)
Press Enter -> copy the formula for the remaining values to get the results:
In the cell where you want to round using the Trunc function, enter the formula: = Trunc(B7)
Press Enter -> copy the formula for the remaining values to get the results:
Above is a detailed guide on rounding numbers in Excel using various Excel functions. Wish you success!
