Round 0.5 towards Even Per ASTM-E29

February 25, 2022 - by Bill Jelen

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

This article is an excerpt from Power Excel With MrExcel

Title photo by Joel Fulgencio on Unsplash