The ROUND function rounds values to decimal places on either side of the decimal point. It is useful and popular. The MROUND function is meant to allow you more flexibility in your rounding calculations. Let’s say you want round to closest 0.05. The MROUND is meant to handle this calculation but unfortunately it provides inconsistent results.
The table below shows the problem (column C) and the solution (column F) which is a tweak of the ROUND function.
When rounding it’s always the values in between the round number that cause the issues.
The ROUND results in Column F are consistent whereas the MROUND results in column C are not. Rows 2 and 3 are OK, but after that there are inconsistencies.
The tweak with the ROUND function is to divide the value by the rounding number and then round to zero decimal places. Then multiply the result by the rounding number – the results of this technique are shown in column F above – the formula from column F is shown in column G.
Thank you for sharing, a great idea
The problem is the floating-point representation that Excel uses to store floating-point numbers. The fractional part 0.025 is actually stored as 0.49999999999986E-02, hence MROUND rounds it down. I don’t think you can see this representational problem in Excel itself, but if you switch to the VBA editor and execute this in the Immediate Window…
? 30.025 – 30
you will see the problem in the printed result. What I am surprised about is that the designers of MROUND did not take this into account and perform a significant figure rounding within the underlying executing code for MROUND… it would not have been that hard for them to have done that. I mean, given the function MROUND is supposed to perform, what good is it if the number being processed is ever so slightly different than the intended value.
A follow-up — I was wrong, you can show the problem directly in Excel. Put this into a cell…
=30.025-30
and change the cell’s format to Number with 17 decimal places.
Argh! I just noticed in my original reply that I typed 0.49999999999986E-02… that was supposed to be 2.49999999999986E-02.