Buzz
Working with Excel may require rounding numbers for work purposes, and depending on the need, you may need to round according to different requirements. Excel now supports many rounding functions for you to use and popular like: ROUND(), ROUNDUP(), ROUNDDOWN(), MROUND(), CEILING(), FLOOR(), EVEN(), ODD(), INT(), and TRUNC()
Rounding Functions in Excel
Guide to Using Rounding Functions in Excel
1. How to Use the ROUND() Function to Round Numbers After and Before the Decimal Point
This function helps you round numbers to shorten them based on the number of digits needed, which is quite simple and common
Syntax:
ROUND(Number to round, num_digits)
Where
Illustrative Example:
For the number 1234.5678, when you use the Round function, the result will be as shown below:
2. How to Use the MROUND() Function to Round to the Multiple of Another Number
Syntax:
MROUND(Number to round, multiple)
- Multiple: The number you want to round the given number to the multiple of
- If Number and Multiple have different signs, the function will return an error #NUM!
- If Number and Multiple are equal, the result is the number itself
- MROUND() rounds up if the remainder of dividing Number by Multiple is greater than or equal to 1/2 Multiple, and rounds down if the remainder of dividing Number by Multiple is less than 1/2 Multiple
Illustrative Example:
Suppose you need to round to the multiple of another number, the rounding result will be as illustrated below:
3. How to Use ROUNDDOWN() and ROUNDUP() Functions
These two functions, fundamentally similar to the ROUND() function, differ only in that they round in one direction: ROUNDDOƯ() always rounds a number towards 0, while ROUNDUP() always rounds a number away from 0.
Syntax:
ROUNDDOWN(Number to round, num_digits)
ROUNDUP(Number, num_digits)
Illustrative Example:
You can compare the results between the ROUNDDOWN() and ROUNDUP() functions in the Excel table below:
4. How to Use CEILING() and FLOOR() Functions
These two functions are similar to the MROUND() function - rounding to the nearest multiple of a specified number, with a slight difference in calculation: CEILING() always rounds a number away from 0, while FLOOR() rounds towards 0.
Syntax:
CEILING(Number to round, significance)
FLOOR(Number, significance)
- Significance: The number you want to round Number to the multiple of itself.
- If Number and Significance have different signs, the function will return an error #NUM!
- If Number is a multiple of Significance -> the result is the number itself.
Illustrative Example:
Suppose we need to compare CEILING(), FLOOR() and MROUND() - in this example, consider Significance as the Multiple of MROUND() -> you will get the result as illustrated below:
5. How to Use EVEN() and ODD() Functions for Rounding in Excel
These two functions round very simply. EVEN() rounds to the nearest even integer, while ODD() rounds to the nearest odd integer. Both round away from zero.
Syntax:
EVEN(number)
ODD(number)
Illustrative Example:
Suppose you need to round numbers in Excel and use 2 functions, EVEN and ODD, you will have the result as illustrated below:
6. How to Use INT() and TRUNC() Functions
These two functions are almost similar in usage if you want to round a number to an integer.
Syntax:
Syntax: INT(number)
Syntax: TRUNC(number [, num_digits])
- Num_digits: An integer indicating how you want to truncate the number
- Num_digits > 0 if Number is a decimal number, then Num_digits indicates the number of decimal places you want to keep (after the decimal point)
- Num_digits = 0 or not specified: removes all the decimal part of Number (if any)
- Num_digits less rounds Number to an integer and truncates Number left to a multiple of 10.
The INT() function rounds a number to the nearest integer.
For positive numbers, both INT() and TRUNC() functions yield the same result (Num_digits of TRUNC() = 0 or not specified), but for negative numbers, the two functions will yield completely different results.
Illustrative Example:
INT(123.456) = 123 | TRUNC(123.456) = 123
INT(-123.456) = -124 | TRUNC(-123.456) = -123
When Num_digits is not 0, TRUNC() differs significantly from ROUND() in that: ROUND() rounds the number, while TRUNC() simply removes decimal places without rounding.
Here are some examples illustrating the TRUNC() function for better understanding:
Thus, Mytour has just introduced to you the Guide to Using Rounding Functions in Excel. To round numbers in Excel, you can utilize functions such as the rounding function ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING, FLOOR, EVEN, ODD, INT, and TRUNC.
Furthermore, during your Excel computations, it's essential to grasp the fundamental functions in Excel to ensure effective calculation utilization in Excel. Wishing you success.
