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