Microsoft Office Tutorials and References
In Depth Information
Syntax:
Caution
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.
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.
Search JabSto ::




Custom Search