Inconsistency error in the MROUND function

Copper Contributor

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...

3 Replies

@Fleischbender 

 

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"

Emphasis added.

 

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.

 

 

Indeed I have tried to subtract the “25550 answer” from the “25600 answer” and multiply the difference by 10^(1 gorillion). The difference is still 0, so no floating point or reoccurring remainder induced errors here.

Another funny occurrence happens when you copy a cell containing 25000*1,023 and paste the value, ie. the pure string 25575, into another cell. If you now take MROUND of this cell it returns the wrong number 25550. However, if you now clear the cell and type 25575 by hand, the MROUND formula suddenly returns the correct number 25600.

@Fleischbender 
 
hi, 
 
sorry, late, but may help others ... it's more than one issue mixing up here: 
 
1. FP-math (IEEE 754 figures and calculations) isn't 'witchcraft', but somewhat tricky in many cases, 
2. MS sometimes create funny programs, in older versions there was a threshold in a way that even values a little too short for 'midway' have been rounded up. example: Excel 2010: '=MROUND(25575;25575*2*1,00000000000001)' which is less than midway -> 51150,000000000500000, per definition one would expect '0'. 
3. MS doesn't stick to it's own definitions, the above threshold contradicts <https://support.microsoft.com/en-us/office/mround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427>
4. MS sometimes changes behaviour, i saw your posting when investigating if above applies to new versions too. 
5. FP-math isn't 'school-math', it acts (needs to) with nearby substitutions for most decimal fractions, and thus 25000 * 1.023 becomes 25574.999999999996, you may try 'ROUND( 25000 * 1.023, 11 )' instead. 
6. what you see on screen is not! what's happening behind the scenes, Excel likes to apply some prettyfiing to reduce user irritations which makes it difficult to understand what's happening in detail (Excel won't show you the strange value but 25575 instead). 
7. The prettyfying can be reduced, try '=0,1+0,2-0,3' -> 0,0000000000000000000 vs. '=(0,1+0,2-0,3)'
-> 0,0000000000000000555
8. computers are complex, FP-math also, and results partly not only depend on one program (Excel) but on the OS (Windows), underlying math libraries, patches, your hardware ... expect surprises from time to time. 
9. above are only eight of a zillion strange things with computers, have fun discovering the rest ;) 
 
Best Regards, 
 
 
 
b.