Rounding numbers incorrect

[lgalang]

We are getting different result from Excel and from our SAP system when
rounding numbers. Excel seems to be inconsistent with the mathematical rule
of rounding numbers. For example, rounding 3.245 to 2 decimal places results
to 3.25. This is obviously incorrect because if you round 3.25 to 1 decimal
point, Excel gives you 3.3; if you round 3.245 to 1 decimal point, Excel
gives you 3.2 (different from 3.3 which actually came also from 3.245).

 

[Jan Karel Pieterse]

Hi Lgalang,

For example, rounding 3.245 to 2 decimal places results
to 3.25. This is obviously incorrect because if you round 3.25 to 1 decimal
point, Excel gives you 3.3; if you round 3.245 to 1 decimal point, Excel
gives you 3.2 (different from 3.3 which actually came also from 3.245).

3.245 rounded to 2 decimals should become 3.25, (the next digit is greater than
or equal to 5).

3.245 rounded to 1 decimal should become 3.2 (the next digit is less than 5,
digits to the right of that should be ignored)

You should not do rounding in steps: round to 3, then to 2, then to 1 decimal.
As you've seen, that may deliver a different result from rounding to the needed
number of digits directly.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jerry W. Lewis]

What results are you getting from your SAP system? If it rounds 3.245 to
3.24, then it is likely rounding per the ASTM E-29 standard, that rounds
exact ties to the nearest even rounded number (often recommended in
scientific fields)
http://en.wikipedia.org/wiki/Rounding#Round-to-even_method
Excel rounds exact ties (5) to the next larger number, which seems to be the
more common method in financial circles. The ASTM method (called "Banker's
Rounding" by MS for reasons that are not clear to me) is partially
implemented in the VBA Round function (Excel 2000 and later). A VBA user
defined function that fully implements the ASTM method and can also be called
from the Excel worksheet is given at

http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69

As Jan Karel pointed out, double rounding is strongly discouraged in every
field, since it can easily lead to incorrect results.

The topic of this group is things that cause Excel to crach, which this
doesn't. You would have gotten more responses faster in an appropriate group
such as the ones dealing with Worksheet Functions, Programming (if VBA), or
General Questions.

Jerry