“How to round a number to the nearest decimal place up to 5/10/100/1000 etc.” is one of the important common questions asked by excel users in one or the other forums. To answer this, there is no one formula to round numbers in excel. Excel provides many rounding functions in its formula library for rounding numbers in excel. To name to few – MROUND, ROUNDUP, ROUNDDOWN, ROUND, FLOOR, CEILING, etc. Each of these functions has its own functionalities. In this tutorial, we would learn the excel MROUND rounding function.
What is Rounding of Numbers?
In mathematical terms, rounding of numbers is nothing but to remove or eliminate integer/decimal digits from a number to make a long number shorter. In rounding, although the original number becomes shorter, it is very close and approximate to the original number.
For example, the long number 195.84523566942 can be rounded-off and made like 195.85. In this example, we have removed extra decimal digits. However, the rounded-off value of 195.85 is still very near to the original number 195.84523566942.
Now, when you have an understanding of what is rounding of numbers in general terms, let us begin with our tutorial on using MROUND excel formula to round numbers in excel.
Also Read: ROUNDUP Function in Excel – Rounding Upwards
When to Use MROUND Function in Excel
The excel MROUND formula is a rounding excel function that is used to round a number up or down to a specified multiple.
The output of MROUND function is a number.
Syntax and Arguments
Below is the explanation of the two arguments of excel MROUND function.
- number – In this argument, specify the number that you want excel to round.
- multiple – In this argument, specify the multiple up to which you want to perform rounding.
Using MROUND Excel Formula – Function Examples
In this section of this blog, we would go through some very easy examples to understand the functioning of MROUND formula.
Let’s say the number is 15.77. Now we want to round this number to the nearest whole number. Use the below formula:
The above formula would result the value as 16.
By putting multiple argument as 1, we can round the number to nearest integer.
Similarly, =MROUND(15.33,1) will result into 15 because 15 is the nearest integer value to the 15.33.
Likewise, to round a given number to the decimal value, use the following formula:
The result of the above formula is 15.80.
How MROUND Function Works in Excel?
How the MROUND formula performs rounding in excel depends on what is the remainder by dividing the number by the multiple argument.
- If remainder of number and multiple argument is greater than or equal to half of the multiple argument, then the MROUND function rounds the value up.
- On the other hand, if the remainder is less than half of the multiple argument, then the MROUND function rounds the value down.
Let me explain the above functionality with the help of an example. Example Case – Round 15.77 to the multiple of 0.10.
- Step #1 : One-half of multiple 0.10 is 0.05.
- Step #2 : Remainder by dividing 15.77 by 0.10 comes 0.07.
Since the reminder 0.07 is greater than and equal to the half of multiple 0.50, therefore the MROUND function will round the value upwards.
Simplest way to find remainder in excel is by using the excel’s MOD function.
Do Not Miss This Point
- The MROUND function is also useful to round the time to the nearest minutes like 10 minutes, 15 minutes, etc.
For example, to round the time 2:52 to the nearest 10 minutes, simply enter the minutes in HH:MM format as multiple argument.
- The MROUND function will result in #NUM! (NUM) error if the sign of the two arguments is different. For example – =MROUND(15,-2) or =MROUND(-15,2), both will give #NUM! error.
Thank You 🙂