The following article shows you how to round numbers in Excel, which helps to calculate faster
Depending on how to round the number of digits after and before the comma, there are many number rounded functions in Excel.
1. Round a number using the ROUND () function
- Meaning: Round to the nearest number according to the number of digits to round
- Syntax: ROUND (number, num_digits)
Inside:
+ number is the value to be rounded.
+ num_digits is the value to be rounded. If:
num_digits = 0 => rounds to the nearest integer
num_digits> 0 => rounds to the number of decimal places corresponding to the num_digits value
num_digits = 0 => rounds to the number of integer numbers corresponding to the num_digits value
- Example: Do the rounding of numbers in the table below using the round function
In a cell to calculate enter the formula: = ROUND (D7, E7)
Press Enter -> copy the formula for the remaining values to result:
2. Round a number using the MROUND () function
- Meaning: Round to a multiple of a specified number
- Syntax: MROUND (nunber, multiple)
Inside:
+ number is the value to be rounded
+ multiple: Number to determine the multiple to round to
- Attention:
+ MROUND function is rounded far away from zero if the balance after dividing the number by multiple is greater than or equal to half the value of multiple
+ The argument, argument must be same if not returning #NUM error value
- Example: Round the following values using the MROUND () function
In a cell to calculate, enter the formula: = MROUND (D7, E7)
Press Enter -> copy the formula for the remaining values to result:
3. Round numbers using ROUNDDOWN () and ROUNDUP ()
- Meaning: The use of two similar functions is different:
+ ROUNDDOWN () function always rounds a number in the direction approaching zero
+ ROUNDDOWN () function always rounds a number in the direction going far away from 0
- Syntax:
ROUNDDOWN (number, num_digits)
ROUNDUP (number, num_digits)
Inside:
- number is the value to be rounded
- num_digits: number of digits to round. If:
num_digits = 0 => rounds to the nearest integer
num_digits> 0 => rounds to the number of decimal places corresponding to the num_digits value
num_digits = 0 => rounds to the number of integer numbers corresponding to the num_digits value
- For example:
4. Rounding numbers using CEILING () and FLOOR ()
4.1 CEILING function
Description: The function performs rounding up, away from 0 to the nearest significant multiple.
Syntax: CEILING (number, significance) .
Inside:
- number: The value you want to round, is a required parameter.
- significance: The multiple you want to round, is a required parameter.
Attention:
- If the arguments in the function are not numbers -> the function returns the #VALUE! Error value
- The value rounded up is adjusted away from zero despite the sign of the number is negative. If number is the correct set of significance, do not round it.
- If the number is a negative value and the negative level the value is rounded down away from zero.
- If the number is a negative value and the positive level the value is rounded up to 0.
For example:
Round the values in the following table with multiples in the description:
In a cell to calculate enter the formula: = CEILING (C8, D8)
Press Enter and copy the formula for the remaining values to get the result:
4.2 FLOOR function
The Floor function is similar to the Ceiling function, except that the Floor function rounds down to zero.
5. Rounding numbers using the EVEN () and ODD () functions
- The Even () function is a function that rounds numbers up to the nearest even integer
Syntax: EVEN (number)
Where: number is the value you want to round
- Odd () is a function that rounds numbers up to the nearest odd integer
Syntax: ODD (number)
Where: number is the value you want to round
Example: Rounding the following values by using the even () and odd () functions
In the cell to calculate the value of doing in the Even function enter the formula: = EVEN (B7)
Press Enter -> copy the formula for the remaining values to result:
Similarly in the cell to calculate the rounding value according to Odd (), enter the formula: = ODD (B7)
Press Enter -> copy the formula for the remaining values to result:
6. Round numbers using the INT () and TRUNC () functions
- INT function performs rounding down to the nearest integer.
Syntax: Int (number)
Where: number is the value to be rounded
- The function Trunc performs rounding according to the number of decimal places
Syntax: TRUNC (number, [num_digits])
Inside:
- number is the value to be rounded
- num_digits: is the number of decimal places to be rounded, is an optional parameter with a default value of 0.
For example:
In a cell to calculate rounding value according to Int function, enter the formula: = INT (B7)
Press Enter -> copy the formula for the remaining values to result:
In the cell to calculate rounding value according to the Trunc function enter the formula: = Trunc (B7)
Press Enter -> copy the formula for the remaining values to result:
The above is a detailed guide on how to round numbers in Excel in various ways using Excel functions. Good luck!