Rounding up for 0.6 to 0.9 makes sense—you round up because that’s the closest integer. A decimal of 0.5, however, is a tie. It’s equidistant to the number above and the number below. While we were all taught that 0.5 rounds toward the higher number, ASTM International (formerly known as the American Society for Testing and Materials) suggests that 0.5 should round up half the time and round down half the time.
Why is the “always round 5 up” rule a problem? If you keep doing it over millions of data points, it will introduce a slight upward skew to the numbers. Assuming you’re rounding to create a simple approximation of the data, you should want the total of your rounded numbers to be very close to the total of your original numbers.
My test published on YouTube using Excel’s ROUND function for 1 million numbers with exactly one digit after the decimal point introduced a $50,000 upward bias on $55 million in revenue. If you’re rounding millions of transactions, it can be costly over time.
The experts at the ASTM thought about this problem. They proposed that 0.5 should round up half the time and round down half the time. How do you make sure that half of the ROUND operations go the other way? They suggested that you should always round a 5 toward the even integer. So a number like 1.5 would round up to 2 but a number like 4.5 would round down to 4. It’s a very small change, but adopting this method reduced the $50,000 bias to only $200.
HOW TO GET ASTM ROUNDING
Excel offers the ROUND function. It always rounds a final digit of 5 to the next highest number. Technically, Excel is rounding away from zero: 2.5 rounds to 3 and -2.5 rounds to -3. In Figure 1, a small sample that’s rounded using ROUND in column C ends up 10% too high.
In order to get Excel to round toward the even integer, you would have to use =IF(MOD(A2,1)=0.5,EVEN(A2),ROUND.
Excel VBA offers a ROUND function that follows the ASTM rule of rounding toward even numbers when the final significant digit is a 5. As you can see in column D of Figure 1, the VBA ROUND function produces a total that’s correct. But using VBA requires a computer instead of Excel Online. And you have to hope that everyone using your spreadsheet has allowed macros.
Column E shows one way of doing ASTM rounding in Excel using =IF(MOD(B2,1)=0.5,MROUND(B2,2),ROUND(B2,0)). This formula checks if the digit after the decimal point is 0.5. If it is, then it uses the MROUND function to round to the nearest multiple of 2. Otherwise, it uses the traditional Excel ROUND function.
While this works, I find that I need to look it up every time or add it to a sticky note on my wall. Yet this complicated method will work on a wide variety of endpoints: Excel for Windows, Excel for Mac, Excel Online, and Excel for the phone.
A final solution would be to use the Power Query tools built into Windows versions of Excel. Starting from a range of data, convert the range to a Table using Ctrl+T. Then, on the Data tab, choose Get & Transform Data, from Table or Range.
This opens the Power Query Editor. Select the column with your unrounded number. On the Add Column tab, choose Rounding and then Round (see Figure 2). Select 0 digits. The resulting column will always round toward even.
FORCING POWER QUERY TO MATCH
Some people might prefer Excel’s default rounding and will want Power Query to match this calculation. After rounding in Power Query, click in the Power Query formula bar and change Number.Round([Number], 0) to Number.Round([Number], 0, RoundingModeAwayFromZero).
There is no option to select this in the Power Query interface. But by using View, Formula bar, you can edit the formula to achieve the same rounding that Excel uses.
As an Excel expert and a Power Query novice, I’m always annoyed when something in Power Query doesn’t match something in Excel. In this case, however, it appears that the calculation in Power Query is better than the calculation in Excel.
SF SAYS
Excel’s default rounding introduces a noticeable upward bias to the results.
May 2021
