Forum Discussion

Fleischbender's avatar
Fleischbender
Copper Contributor
Feb 22, 2020

Inconsistency error in the MROUND function

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, sometim...
excel
Formulas and Functions
Fleischbender's avatar
Fleischbender
Copper Contributor
Feb 24, 2020
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.
newbie-02's avatar
newbie-02
Copper Contributor
Oct 17, 2021

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. 

Resources