Forum Discussion
Inconsistency error in the MROUND function
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.
