Excel’s Round() function is not totally accurate. There’s a lot more to rounding numbers than we were taught in school and how Microsoft’s Round() function works.
What we learned in school and Round() is OK. But if you’re dealing with larger groups of numbers, narrow rounding ranges or should be very, very fair (e.g test results or employee rating) then more accurate rounding is important.
At school we were taught rounding to whole numbers this way:
- anything below half (0.5) was rounded down to the next lowest number
- anything half and above was rounded to the next highest number.
And that’s the way Excel’s Round() function works.
But it’s not really accurate because slightly more numbers round up than round down, not the even 50/50 split needed for more evenly distributed rounding.
Yes, I was surprised too. All these years I thought rounding was a simple thing taught in primary school. Turns out rounding has hidden complications …
The Round() bias problem
Why the bias? All middle (0.5) values are rounded up. ‘Midway’ results (e.g. 0.5) should split 50/50 rounded up or down for an even distribution of results.
- If you have numbers from 1 to 10 rounded to either 1 or 10, then only three results are rounded down (2,3,4) while five are rounded up (5,6,7,8, 9).
- For a two-decimal place group of numbers there are 49 possibilities for rounding down (from .01 to .49) but 50 possibilities for rounding up (.50 to .99).
Not a big deal in many cases, but the way Round() works can distort results.
Round() bias on display
To see the Round() bias in action, we made a list of a thousand random numbers from 10 to 11 (Col A) then rounded them to either whole number (Col B). Finally checked the distribution of numbers.
- the average of the rounded numbers (F9) is slightly higher than the expected 10.5
- there are more rounded UP results (G12) than rounded down (G13).
If you recalculate the sheet with different random numbers, the results will change around the 50/50 mark. But the overall trend is to a biased rounding up, the screen image above is a fair representation of that.
To truly fix rounding bias you’d have to alternate between rounding up then down, depending on what happen the last time (i.e. set a flag so that the last midway rounding is remembered). That would be possible in VBA but there’s an easier and internationally approved method.
The approved fix for rounding bias
The approved way to avoid rounding bias is ‘Ties to Even’ so that half the mid-way numbers are rounded down, the other half are rounded up.
We say ‘approved’ because it’s part of an international standard called IEEE Standard for Floating-Point Arithmetic (IEEE 754) also known as ISO/IEC 60559:2020 which standardizes the way to store and work with numbers given the in-built limitations of binary computers.
Amazingly, Excel does NOT support the IEEE standard ‘Ties to Even’ but there’s a formula to do it see More accurate Excel rounding to the international specification
All about Ceiling() and Floor() Excel rounding options
All about MROUND() in Excel
Excel’s Round () and five other rounding functions
