Microsoft Office Tutorials and References
In Depth Information
If you remember how they taught you to round in school, you know the
rule that numbers ending in 0.5 should always round up. The Excel de-
velopers must have sat through the same curriculum as you and I did,
because they implemented rounding in this manner.
However, if you have a large amount of data points that end in 0.5, you
will introduce a fair amount of error in the data by using the method
that we learned in school. In Figure 11.9 , a million data points end with
a single decimal place. Comparing the total of the points and the total
of the ROUND of the data points shows a delta of nine hundredths of a
percent. In this example, the rounded values total to $52,077 more than
the original values.
Figure 11.9. ROUND
ROUND skews your data.
skews your data.
A set of rules known as Bankers ’ Roundingor ASTME29Rounding
prescribes that values ending in 0.5 should always be rounded to the
nearest even integer. Thus, 1.5 would round up to 2 and 2.5 would round
down to 2. Column D of Figure 11.9 contrasts the Bankers ’ Round-
ing method with the regular rounding method. This formula produces a
result that is 91 times more accurate for this data set. The rounded
values in column D are within 9.9 ten-thousandths of a percent for a
total error of only $572 over the million rows of data.