Microsoft KB Archive/277605

= Incorrect value is calculated when the thousand separator is the same as the decimal separator in Excel =

Article ID: 277605

Article Last Modified on 2/6/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q277605





SYMPTOMS
In Microsoft Excel, when you change the decimal separator to a comma, or when 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. Additionally, calculated values may be increased by a factor of 1,000.



CAUSE
This problem 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.

Note In Microsoft Office Excel 2007, click the Microsoft Office Button, and then click Excel Options. On the International tab, change the comma to a period in the Thousands separator box, and then click OK.

Note In Excel 2007, click Advanced in the Excel Options dialog box. Then, change the comma to a period in the Thousands separator box under the Editing options, and then click OK.

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

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

<div class="resolution_section">

RESOLUTION
To resolve this problem, use one of the following methods:
 * Make sure that the decimal separator and the thousand separator are different characters.
 * Make sure that the number of integers that come after the decimal separator is two or less.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Additional query words: XL2002 XL2003 XL2007

Keywords: kbprb kbtshoot kbexpertisebeginner kbbug kbpending KB277605

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.