Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 277605 - Last Review: February 6, 2007 - Revision: 2.2
Incorrect value is calculated when the thousand separator is the same as the decimal separator in Excel
This article was previously published under Q277605
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.
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.
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
APPLIES TO
- Microsoft Office Excel 2007
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
| kbprb kbtshoot kbexpertisebeginner kbbug kbpending KB277605 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please