Rounding numbers in Excel is a common exercise that every Excel user does very frequently. But I've seen that most Excel users do not use the most appropriate functions. Most users are not even aware about the existence of multiple excel functions dedicated to rounding of numbers according to different situations and requirements.
So, in this article I provide you with 10 excel functions that you can use in different situations to make your task of rounding of numbers in Excel seem like a cake-walk. Do have a look and practice these to understand the differences between these and the situations in which each of these have to be used.
(This article was first published on the author's blog - MadAboutExcel.com. Click here to visit the blog and get the author's 'Ultimate Excel Shortcuts Course - Get 10X Faster on Excel' for FREE!!)
ROUND | ROUNDUP | ROUNDDOWN
ROUND function rounds a number to a specified number of digits.
The syntax of the ROUND function is = ROUND(number, number of digits). The 'number of digits' is the number of digits after decimal that you want to have in the result. Note that if number of digits is less than 0, the number is rounded to the left of the decimal point. Also, if number of digits is zero the number is rounded to the nearest integer.
Looking at the below mentioned examples of the usage of this function along with the results will help you understand this function better.
| =ROUND(15.4, 1) | 15.4 |
| =ROUND(17.218, 0) | 17 |
| =ROUND(-24.358, 2) | -24.36 |
| =ROUND(36.5, -1) | 40 |
| =ROUND(843.3,-3) | 1000 |
| =ROUND(7.64,-1) | 10 |
| =ROUND(-58.91,-2) | -100 |
If you want to always round up (i.e. away from zero), use the ROUNDUP function and if you want to always round down (i.e. towards zero), use the ROUNDOWN function.
The syntax of ROUNDUP and ROUNDDOWN functions is same as that of ROUND function. These functions are similar to ROUND, except that they always round up and round down a number respectively.
| =ROUNDUP(28.3,0) | 29 | =ROUNDDOWN(28.7,0) | 28 |
| =ROUNDUP(6.26789, 3) | 6.268 | =ROUNDDOWN(6.76789, 3) | 6.767 |
| =ROUNDUP(-8.147832, 1) | -8.2 | =ROUNDDOWN(-8.67832, 1) | -8.6 |
| =ROUNDUP(67546.48943, -2) | 67600 | =ROUNDDOWN(67546.68943, -2) | 67500 |
TRUNC
TRUNC function truncates (as the name suggests) a number to an integer by removing its fractional part.
The syntax of TRUNC function is =TRUNC(number, number of digits), where 'number of digits' is optional. The 'number of digits' is the number of digits after decimal that you want to have in the result. Note that if number of digits is less than 0, the number is rounded to the left of the decimal point (similar to ROUND, ROUNDUP and ROUNDDOWN).
Looking at the below mentioned examples of the usage of this function along with the results will help you understand this function better.
| =TRUNC(10.8) | |
| =TRUNC(10.8,0) | |
| =TRUNC(-10.8) | |
| =TRUNC(0.38) | 0 |
| =TRUNC(364.17,1) | 364.1 |
| =TRUNC(364.17,-1) | 360 |
| =TRUNC(364.643) | 364 |
| =TRUNC(-364.76,1) | -364.7 |
INT
INT function rounds a number DOWN to the nearest integer.
The syntax of INT function is =INT(number or reference to the number)
So, the result for =INT(12.7) will be 12 and that for =INT(-12.7) will be -13 (Rounding a negative number down will round it away from zero).
ODD | EVEN
ODD function rounds a number UP to the nearest odd integer.
The syntax of ODD function is =ODD(number)
So, the result for =ODD(11.7) will be 13 and that for =ODD(-11.7) will be -13 (Rounding a negative number down will round it away from zero).
EVEN function functions in similar way. The only difference is that it rounds a number up to the nearest even integer.
MROUND
MROUND function returns a number rounded to the nearest multiple of the specified number.
The syntax of the MROUND function is =MROUND(number, multiple)
So, =MROUND(21,4) will result in 20 (being the nearest multiple of 4 to 21). Similarly, =MROUND(-21,-4) will result in -20.
Please remember that if there are two possible results, then Excel will round the number up (and not down). So, =MROUND(21,2) will result in 22 (and not 20).
If number and multiple have different signs, the result will be #NUM!
FLOOR | CEILING
FLOOR function rounds a number towards zero to the nearest multiple of 'significance'.
The syntax of FLOOR function is =FLOOR(number, significance), where number is the number you want to round and significance is the multiple to which you want to round.
So, =FLOOR(15.8,2) will round the number 15.8 towards zero to a multiple of 2 i.e. the result will be 14. Similarly, the result of =FLOOR(3.76,0.1) will be 3.7.
=FLOOR(-5.4,-2) will result in -4 (towards zero).
If the sign of number is negative, a value is rounded down and adjusted away from zero. So, the result of =FLOOR(-16.87,3) will be 18.
If number and significance have different signs, the result will be #NUM!
CEILING function is just the opposite of FLOOR function with the difference being that CEILING function rounds the number away from zero, to the nearest multiple of significance.
FINAL WORDS
So these were 10 different excel functions to help you round numbers easily. Practice these well and understand the difference between them. Many of these can seem to be performing similar tasks. But trust me when I say that each of the above discussed functions have their own distinct usage. It was difficult for me to jot down each and every possible situation and the best function to use in that situation. So I have left that task for you to do. Reading this article and the practicing these functions will definitely make your concept clearer. And if you still have doubts, I am there to help you!
If you think this article can help your friends and colleagues, please share the articles on Facebook / Twitter / LinkedIn using the buttons above / below the article.
I'll see you soon with another such article.
Till then. Happy Excelling!!
CAclubindia
