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

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.