Excel: Round 0.5 towards Even Per ASTM-E29

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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).

  1. Round .5 towards the even number.