MROUND Function forms a part of family of Rounding Functions in Google Sheet. It helps you return the nearest multiple of value with a specified factor.
Hence, its functionally closer to CEILING and FLOOR functions compared to ROUND Function.
Lets deep dive into the fundamentals of MROUND function and how can we put it to good use.
Table of Contents
Purpose of MROUND Function
As described before MROUND Returns the nearest multiple of a factor from a number.
Syntax and Parameter Definition
= MROUND(value, [factor])- value: The number from which closest integer multiple of [factor] is required
- [factor]: The number whose integer multiple will be benchmarked with value.
Note:
- Both Value and Factor should be either positive or negative simultaneously. A combination of positive and negative will result in a error.
- Both Value & Factor can take only numerical input
- Any form of non-numerical input(like “abc”, “xyz” etc) for value will result in an “#VALUE!” error.
- [factor] is an optional input. 1 is it’s default value.
- You can directly add static values or cell references for both value and [factor]
Expected Output, logic behind it and Examples
MROUND Function checks for all integer multiples of the supplied factor. And the multiple which is closest to the value is retuned.
Say, we have the below example:
=MROUND(10,3)Let’s now list all integer multiples of 3:-
- 3*1 = 3
- 3*2 =6
- 3*3 = 9
- 3*4 = 12
Clearly 10 lies between 9 & 12. And, it’s a no brainer that it is closer to 9 than 12.
Hence, the output of MROUND(10,3) will be 9.
Similar Value Different Factor Example – MROUND Function
As we can clearly see below MROUND Functions outputs the nearest multiple of factors with the value(col B)
In the first case, 5*205 = 1025. Now the other multiple also can be 5*204 = 1020. Since 1025 is closer to 1023.458 than 1020, hence 1025 is out desired output.
Similarly for other Examples we have tweaked the Factor values to 10,0.5,0.7,0.9. The factors may change but each time MROUND gives us the output which is the nearest multiple of factor with value.
Working with Negative Value & Factor
Value and Factor have to be negative and positive at the same time. Any other combination(like case 1 below) results in an ‘#NUM!’ error.
Other than this there’s no difference of treatment of positive/negative inputs in MROUND. As we can see before, the ouputs for all the cases are very much in the expected lines.
Working with Zero Value/Factor
Simple rule of thumb. If either Value/Factor is zero then the output of MROUND is 0.
And, if you were to give a though about it it’s not very difficult to understand either. Say, the value is 0, then it’s clear that you need to multiply Factor by 0 as well to make it equal to value.
When the Factor is 0, then all it’s multiples are bound to be zero. Hence, the closest multiple will be 0 as there are no other multiples present.
Visual Demo of MROUND Function
Before we end here is a sample visual demonstration of MROUND Function in action.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂
