- Excel Book Excerpt
Excel Round 0.5 towards Even Per ASTM-E29
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Round 0.5 towards Even Per ASTM-E29
Problem: Excel always rounds 0.5 up to the next integer. The latest best practice in rounding says to round 0.5 towards the even number.
Back in school, you probably learned to round 0.5 up to the next highest number. In a large data set, this rule is leading to the data set being slightly skewed higher. The guidance published by the ASTM in their rule E29 says that numbers ending in 0.5 should round towards the even number. Theoretically, half the time the number rounds up and half the time the number rounds down, cancelling out the skew.
Strategy: Use =IF(MOD(A2,1)=0.5,MROUND(A2,2),ROUND(A2,0)) instead of ROUND(A2,2).
Figure 354 Round .5 towards the even number.
For more resources for Microsoft Excel: