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).
- Round .5 towards the even number.