02-22-2020 06:00 AM
02-22-2020 06:00 AM
I think I have found an error in the MROUND function. The error occurs when the number to be rounded is 25575 and the integer is 50. In this case, MROUND is supposed to return 25600. However, sometimes it returns 25550 instead. Specifically, MROUND(25575;50) = 25600, but MROUND(25000*1.023;50) = 25500, even though 25000*1.023 = 25575.
According to the Office Support page for MROUND,"MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple". In this case the remainder is abs(25575-25600)=abs(25575-25575)=25. As 25/50=0.5, MROUND is supposed to round up. However, as shown in the above paragraph, whether or not MROUND rounds down or up, may depend on whether the input is a string number or a product of to factors.
This inconsistency does not happen independently of input numbers. For the numbers I've tried, there is only the obove case in which the error occurs. The Excel versions of which this error has occured is Excel 365 and Excel 2016.
Attached is an Excel-file that shows the issue. Link to Office Support page for MROUND: https://support.office.com/en-us/article/mround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427?NS=EXC...
02-22-2020 08:53 AM
Interesting, to be sure.
You yourself say it doesn't happen all the time, even with the numbers you cite.
"However, sometimes it returns 25550 instead"
You've no doubt run out extended decimal places as I just did to see if somehow there's a digit added at the end.....If indeed it's only "sometimes" that this aberration occurs, it may just be a function of "static" in the system.
It's also significant (from a diagnostic point of view) that you've not been able to produce this error with any other numbers. Given that 256 is one of those numbers in the binary sequence--2,4,8,16,32,64,128,256,512 etc--I'm wondering if that in and of itself is a possible causative factor.
02-23-2020 06:16 PM