XL2002: Incorrect Value Is Calculated When the Thousand Separator Is the Same As the Decimal Separator (277605)

SYMPTOMS

In Microsoft Excel, when you change the decimal separator to a comma, or you change the thousand separator to a period, the values that you enter into a cell may be increased by a factor of 1,000, and calculated values may be increased by a factor of 1,000.

CAUSE

This behavior can occur when the decimal separator and the thousand separator are the same symbol, and the number of integers after the decimal separator is greater than two, as in the following example:

In a new Excel worksheet, type 1,234.56 in cell A1.
In cell B1, type the following equation:
=A1*3
B1 returns the value 3703.68.
On the Tools menu, click Options.
On the International tab, in the Thousands separator box, change the comma to a period, and then click OK.

You may need to clear the Use System Separators box so that the Decimal and Thousands boxes are not dimmed.

The decimal separator and the thousands separator are now identical.
Select cell A1 and add a 7 to the string so that the value reads 1234.567, and then press ENTER. Note that the formula in cell B1 returns 3703701; the value has increased by a factor of 1000.

RESOLUTION

To resolve the issue, use one of the following methods.

Method 1

Ensure that the decimal separator and the thousand separator are different characters.

Method 2

Ensure that the number of integers after the decimal separator is two or less.