Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


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:
  1. In a new Excel worksheet, type 1,234.56 in cell A1.
  2. In cell B1, type the following equation:
    =A1*3
    B1 returns the value 3703.68.
  3. On the Tools menu, click Options.

    Note In Microsoft Office Excel 2007, click the Microsoft Office Button, and then click Excel Options.
  4. 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.
  5. 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.

↑ Back to the top


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.

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top


Keywords: KB277605, kbpending, kbbug, kbexpertisebeginner, kbtshoot, kbprb

↑ Back to the top

Article Info
Article ID : 277605
Revision : 4
Created on : 2/6/2007
Published on : 2/6/2007
Exists online : False
Views : 326