MROUND in Excel (Table of Contents)
What is MROUND in Excel?
MROUND is an Excel function categorized under math & trigonometric function in Excel. Commonly this function is used with numeric values. The function operates in such a way to round the supplied number up or down to a mentioned multiple within the formula. If you want to round a number according to a multiple this function can be used. This will round the number towards 0 or away from 0 according to the different situations.
Syntax:
Arguments of MROUND Function:
- Number: The numerical value that you want to convert or want to round.
- Multiple: The multiple to which you round the specified number.
If you look at the process or how it is operating among the provided data, the MROUND function will round your number supplied into a perfect multiple up or down to the given multiple. As per the condition the multiple can select towards 0 or away from 0. You might get confused about how this can be selected or what could be the criteria to select a multiple greater than the number to be rounded and what will be to select a number less than the given number. For this, you can refer to the two conditions explained below. The given number will fall in any of the below condition.
- Divide the number you want to round with the multiple. If the remainder is greater than or equal to the half of the multiple, then round the number up.
- Divide the number you want to round with the multiple. If the remainder is less than or equal to the half of the multiple, then round the number down.
If the formula applied is MROUND= (7,4). This rounds the number 7 into the nearest multiple of 4. The result will be 8. For more clarification, you can go through the detailed examples.
How to Apply MROUND Function in Excel?
Let’s understand how to apply the MROUND in Excel with some examples.
Example #1 – Rounding Up Multiple of a Number
Before getting into complicated calculations let’s learn the logic of MROUND function. For example, you have given the number 15 and want to round it into the multiple of 6. So the formula applied will be:
MROUND= (15,6)
You may be getting into confusion like whether the number round into 18 or 12. 18 is the nearest multiple of 6 away from 0 and 12 is also a multiple towards 0. Both are two nearest multiple up (away from 0) and down (towards 0) to the number you want to round. So which number could select as the result? In such a situation you can go through the two different conditions given below and select the condition which is appropriate to the data given.
Divide 15 the number you want to round with the multiple 6. It depends on the value of the reminder to select the condition.
- If the remainder is greater than or equal to half of the multiple, then round the number which is away from zero.
- If the remainder is less than the multiple, then round the number which is towards zero.
Here the remainder is 3. Check whether the reminder is greater than or equal to the half of the multiple. Multiple is 6 and half of the multiple is 3, in this situation you can go ahead with the first condition that is round the number up or away from zero.
Example 2: Rounding Times
You can apply the MROUND within time to the nearest 5 and 10 minutes. The time is provided and let’s apply the formula to it.
- The time is given in (hh: mm: ss AM/PM) format so the minute is mentioned “0:05” in the formula. The formula is ‘=MROUND(A2, “0:05”)’ if the result shows some other values change the format into time as you given the time format.
- After applying the formula answer is shown below.
- Drag the same formula in cell B2 to B9.
2. The results are rounded accordingly and the result is calculated depending on the same rules of the reminder value.
- In the same way, 10 minutes is rounded and the formula is ‘=MROUND(B2, “0:10”)’.
- After applying the formula answer is shown below.
4. We will be applying the same formula to entire cells and the final result is as shown:
Example #3 – MROUND Function on Price
Through this example, you can learn how the MROUND function can be used to round the fraction of price or amount while making billing towards large amount. This is used to provide discounts on the fraction of amount or rounding the amount into a perfect figure.
- In a big deal of amounts to avoid the small sum of prices, you can use this MROUND function. This is an effective method to apply according to the discounts that you provide or a fraction of the amount you want to neglect. The formula applied is ‘=MROUND(A2,0.05)’
- After applying the formula answer is shown below.
- Drag the same formula in cell B2 to B7.
- Apply the below formula in cell C2.
- After applying the formula answer is shown below.
- Drag the same formula in cell C2 to C7.
Things to Remember About MROUND in Excel
- MROUND function works only with numbers, both the arguments should be numbers.
- #NAME?” will be the error produced if any one of the parameters is not numeric.
- If any signs like positive negative are used along with any one of the parameters or both it will show an error “#NUM!”.
Recommended Articles
This is a guide to MROUND in Excel. Here we discuss how to apply MROUND in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
