Value 1 in excel cell show as 0.99999999999999978 once save in XMLformat

  • Thread starter Thread starter jeffchongonly
  • Start date Start date
J

jeffchongonly

Hi all,

I receive an excel sheet , with a column which is format in number.
The value that i seen from the screen is '1'. Later, when i save the
file in . xml format (File->Save As, choose 'XML Spredsheet' .xml' ) i
found that the '1' had become '0.99999999999999978'.

I relook into the spread sheet again, the value is really show in '1'
in the formula bar, and double check on the format it is formatted in
number with zero decimal place.

I don't have a clue where the '0.99999999999999978' is come from ?
Could it be an excel bug ? or due to user is using some kind of
different IME to input the value '1' ?


I paste part of the XML contents below,
If you scroll down into last few rows, u can see that this line show
number as '0.99999999999999978'
<Cell ss:StyleID="s69"><Data ss:Type="Number">0.99999999999999978</
Data></Cell>


kind regards,
TOTO

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>ketant</Author>
<LastAuthor>User</LastAuthor>
<Created>2006-03-10T09:38:56Z</Created>
<LastSaved>2009-03-10T02:57:59Z</LastSaved>
<Company>Hewlett-Packard</Company>
<Version>11.9999</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11640</WindowHeight>
<WindowWidth>15480</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>315</WindowTopY>
<ActiveSheet>1</ActiveSheet>
<FirstVisibleSheet>1</FirstVisibleSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font x:Family="Swiss"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s41" ss:Name="³£¹æ_mapping">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Helv" x:Family="Swiss"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s65">
<Borders/>
</Style>
<Style ss:ID="s66">
<Borders/>
<Interior/>
</Style>
<Style ss:ID="s67">
<Borders/>
<NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s68">
<Borders/>
<NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-
&quot;??_);_(@_)"/>
</Style>
<Style ss:ID="s69">
<Borders/>
<NumberFormat ss:Format="0"/>
</Style>
<Style ss:ID="s70">
<Borders/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s71" ss:Parent="s41">
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
<Borders/>
<Font x:Family="Swiss" ss:Size="9" ss:Color="#000000"/>
<Interior/>
<NumberFormat ss:Format="mm\/dd\/yyyy"/>
</Style>
</Styles>
<Worksheet ss:Name="VERSION">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1"
x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell><Data ss:Type="String">NAME HIDDEN DOCUMENT</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Visible>SheetHidden</Visible>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="MVC_SHIPMENT_UPLOAD">
<Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="501"
x:FullColumns="1"
x:FullRows="1" ss:StyleID="s65">
<Column ss:StyleID="s65" ss:Width="51.75"/>
<Column ss:StyleID="s65" ss:Width="96"/>
<Column ss:StyleID="s65" ss:Width="87.75" ss:Span="1"/>
<Column ss:Index="5" ss:StyleID="s65" ss:Width="86.25"/>
<Column ss:StyleID="s65" ss:Width="78.75"/>
<Column ss:StyleID="s65" ss:Width="39.75"/>
<Column ss:StyleID="s65" ss:Width="108.75"/>
<Column ss:StyleID="s65" ss:Width="168"/>
<Column ss:StyleID="s65" ss:Width="176.25"/>
<Column ss:StyleID="s65" ss:Width="63.75"/>
<Column ss:StyleID="s65" ss:Width="75.75"/>
<Column ss:StyleID="s65" ss:Width="114.75"/>
<Column ss:StyleID="s65" ss:Width="126"/>
<Row>
<Cell><Data ss:Type="String">COUNTRY</Data></Cell>
<Cell><Data ss:Type="String">MONTH</Data></Cell>
<Cell><Data ss:Type="String">YEAR</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">RNAME</Data></Cell>
<Cell><Data ss:Type="String">ACCNAME</Data></Cell>
<Cell><Data ss:Type="String">DNUMBER</Data></Cell>
<Cell><Data ss:Type="String">SID</Data></Cell>
<Cell><Data ss:Type="String">SQTY</Data></Cell>
<Cell><Data ss:Type="String">PRICE_LC</Data></Cell>
<Cell><Data ss:Type="String">END_USER_PRICE_USD</Data></Cell>
<Cell><Data ss:Type="String">INV_NO</Data></Cell>
<Cell><Data ss:Type="String">INV_DATE</Data></Cell>
<Cell><Data ss:Type="String">DORDER_NO</Data></Cell>
<Cell><Data ss:Type="String">DORDER_DATE</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">ERROR</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">China</Data></Cell>
<Cell><Data ss:Type="String">FEB</Data></Cell>
<Cell><Data ss:Type="Number">2009</Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxyy company</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxtt uni</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">a-bb-ccc-00000</
Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">abc1234</Data></
Cell>
<Cell ss:StyleID="s69"><Data ss:Type="Number">8</Data></Cell>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s70"><Data ss:Type="String">130006265003411321</
Data></Cell>
<Cell ss:StyleID="s71"><Data
ss:Type="DateTime">2009-02-25T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s69"/>
<Cell ss:StyleID="s67"/>
</Row>
<Row>
<Cell><Data ss:Type="String">China</Data></Cell>
<Cell><Data ss:Type="String">FEB</Data></Cell>
<Cell><Data ss:Type="Number">2009</Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxyy company</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxtt uni</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">a-bb-ccc-00000</
Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">abc1234</Data></
Cell>
<Cell ss:StyleID="s69"><Data ss:Type="Number">0.99999999999999978</
Data></Cell>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s70"><Data ss:Type="String">130006265003411321</
Data></Cell>
<Cell ss:StyleID="s71"><Data
ss:Type="DateTime">2009-02-25T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s69"/>
<Cell ss:StyleID="s67"/>
</Row>
....
 
I relook into the spread sheet again, the value is really show in '1'
in the formula bar, and double check on the format it is formatted in
number with zero decimal place.

I don't have a clue where the '0.99999999999999978' is come from ?
Could it be an excel bug ?
Click to expand...

No, but it is a common source of misunderstanding.

In a nutshell, you are confusing the __displayed__ value with the __actual__
value. Let's take a simple; you can follow along as an experiment. Enter
the number 1.5 into a cell; then format the cell as Number with 0 decimal
places. You will see 2. But you know the real value is 1.5 because you
entered it as such. As proof, change the format to Number with 1 decimal
place. You will see 1.5. Where did that come from? Answer: it was always
there. (By the way, all of this assumes that you do __not__ set the
calculation option "Precision as displayed".)

The same thing is happening in your case. But it is more mysterious because
the value in the cell is the result of a formula; you expect a result of 1,
and you do understand where the 0.99999999999999978 is coming from.
Moreover, if we return to the 1.5-v-2 experiment above, copy-and-paste
probably transferred 2, not 1.5. (At least, that is what happens when I
paste into Notepad.)

The answer is complicated, and there can be two reasons for the "odd"
result.

In part, it might be due to the fact that in Excel, most decimal fractions
cannot be represented exactly; for example, 1.1 is really represented
exactly as
1.10000000000000,0088817841970012523233890533447265625 (the comma demarcates
15 significant digits to the left). Close, but not exact. Such
infinitesimal differences cause numerical abberations in computations.
Eventually, they can become significant.

And in part, the inexact results can be due to the fact that in Excel,
arithmetic with decimal fractions or that results in decimal fractions
cannot be represented exactly in most cases.

Add to all of this the fact that apparently applications use heuristics to
decide when to copy or paste the displayed value or the actual value. (I
think the heuristic applies to the paste operation. So pasting into one
application might yield a different result than another application.)

In any case, the point is: you have a situation where the displayed value
does not match exactly the actual value. Copy-and-paste copies the actual
value (but sometimes not exactly), not the displayed value.

What can you do? That depends on your needs.

The simplest solution is to ensure that the displayed and actual values
agree; at least within 15 significant digits, which is the best that you can
do. This can be accomplished either by using ROUND prolifically in your
worksheet or by setting the calculation "Precision as displayed" (Tools >
Options > Calculation). I used to avoid the latter because it is so poorly
documented, leaving many unanswered questions. As I have filled in the
blanks over time, I am thinking that PAD might not be as risky as I once
thought. But I am still wary of it. So I use ROUND.

HTH.


----- original message -----

Hi all,

I receive an excel sheet , with a column which is format in number.
The value that i seen from the screen is '1'. Later, when i save the
file in . xml format (File->Save As, choose 'XML Spredsheet' .xml' ) i
found that the '1' had become '0.99999999999999978'.

I relook into the spread sheet again, the value is really show in '1'
in the formula bar, and double check on the format it is formatted in
number with zero decimal place.

I don't have a clue where the '0.99999999999999978' is come from ?
Could it be an excel bug ? or due to user is using some kind of
different IME to input the value '1' ?


I paste part of the XML contents below,
If you scroll down into last few rows, u can see that this line show
number as '0.99999999999999978'
<Cell ss:StyleID="s69"><Data ss:Type="Number">0.99999999999999978</
Data></Cell>


kind regards,
TOTO

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>ketant</Author>
<LastAuthor>User</LastAuthor>
<Created>2006-03-10T09:38:56Z</Created>
<LastSaved>2009-03-10T02:57:59Z</LastSaved>
<Company>Hewlett-Packard</Company>
<Version>11.9999</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11640</WindowHeight>
<WindowWidth>15480</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>315</WindowTopY>
<ActiveSheet>1</ActiveSheet>
<FirstVisibleSheet>1</FirstVisibleSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font x:Family="Swiss"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s41" ss:Name="³£¹æ_mapping">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Helv" x:Family="Swiss"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s65">
<Borders/>
</Style>
<Style ss:ID="s66">
<Borders/>
<Interior/>
</Style>
<Style ss:ID="s67">
<Borders/>
<NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s68">
<Borders/>
<NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-
&quot;??_);_(@_)"/>
</Style>
<Style ss:ID="s69">
<Borders/>
<NumberFormat ss:Format="0"/>
</Style>
<Style ss:ID="s70">
<Borders/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s71" ss:Parent="s41">
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
<Borders/>
<Font x:Family="Swiss" ss:Size="9" ss:Color="#000000"/>
<Interior/>
<NumberFormat ss:Format="mm\/dd\/yyyy"/>
</Style>
</Styles>
<Worksheet ss:Name="VERSION">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1"
x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell><Data ss:Type="String">NAME HIDDEN DOCUMENT</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Visible>SheetHidden</Visible>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="MVC_SHIPMENT_UPLOAD">
<Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="501"
x:FullColumns="1"
x:FullRows="1" ss:StyleID="s65">
<Column ss:StyleID="s65" ss:Width="51.75"/>
<Column ss:StyleID="s65" ss:Width="96"/>
<Column ss:StyleID="s65" ss:Width="87.75" ss:Span="1"/>
<Column ss:Index="5" ss:StyleID="s65" ss:Width="86.25"/>
<Column ss:StyleID="s65" ss:Width="78.75"/>
<Column ss:StyleID="s65" ss:Width="39.75"/>
<Column ss:StyleID="s65" ss:Width="108.75"/>
<Column ss:StyleID="s65" ss:Width="168"/>
<Column ss:StyleID="s65" ss:Width="176.25"/>
<Column ss:StyleID="s65" ss:Width="63.75"/>
<Column ss:StyleID="s65" ss:Width="75.75"/>
<Column ss:StyleID="s65" ss:Width="114.75"/>
<Column ss:StyleID="s65" ss:Width="126"/>
<Row>
<Cell><Data ss:Type="String">COUNTRY</Data></Cell>
<Cell><Data ss:Type="String">MONTH</Data></Cell>
<Cell><Data ss:Type="String">YEAR</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">RNAME</Data></Cell>
<Cell><Data ss:Type="String">ACCNAME</Data></Cell>
<Cell><Data ss:Type="String">DNUMBER</Data></Cell>
<Cell><Data ss:Type="String">SID</Data></Cell>
<Cell><Data ss:Type="String">SQTY</Data></Cell>
<Cell><Data ss:Type="String">PRICE_LC</Data></Cell>
<Cell><Data ss:Type="String">END_USER_PRICE_USD</Data></Cell>
<Cell><Data ss:Type="String">INV_NO</Data></Cell>
<Cell><Data ss:Type="String">INV_DATE</Data></Cell>
<Cell><Data ss:Type="String">DORDER_NO</Data></Cell>
<Cell><Data ss:Type="String">DORDER_DATE</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">ERROR</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">China</Data></Cell>
<Cell><Data ss:Type="String">FEB</Data></Cell>
<Cell><Data ss:Type="Number">2009</Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxyy company</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxtt uni</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">a-bb-ccc-00000</
Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">abc1234</Data></
Cell>
<Cell ss:StyleID="s69"><Data ss:Type="Number">8</Data></Cell>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s70"><Data ss:Type="String">130006265003411321</
Data></Cell>
<Cell ss:StyleID="s71"><Data
ss:Type="DateTime">2009-02-25T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s69"/>
<Cell ss:StyleID="s67"/>
</Row>
<Row>
<Cell><Data ss:Type="String">China</Data></Cell>
<Cell><Data ss:Type="String">FEB</Data></Cell>
<Cell><Data ss:Type="Number">2009</Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxyy company</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">xxtt uni</Data></
Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">a-bb-ccc-00000</
Data></Cell>
<Cell ss:StyleID="s70"><Data ss:Type="String">abc1234</Data></
Cell>
<Cell ss:StyleID="s69"><Data ss:Type="Number">0.99999999999999978</
Data></Cell>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s68"/>
<Cell ss:StyleID="s70"><Data ss:Type="String">130006265003411321</
Data></Cell>
<Cell ss:StyleID="s71"><Data
ss:Type="DateTime">2009-02-25T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s69"/>
<Cell ss:StyleID="s67"/>
</Row>
....
 
Any decimal fraction or result of calculations that involed decimal fractions
may have digits beyond the 15th that would surpise you. This is due to the
fact that computers (including Excel) work in binary. Excel tries to gloss
over this by displaying no more than 15 significant digits, but apparently
the XML converter has no such display restriction.

What version of Excel are you using? I can verify that this occurs with
Excel 2003, but do not know if the behavior was changed in 2007. Rounding
will prevent it, as suggested by JoeU.

Jerry
 
Thanks Joe and Jerry,

Yes, i am using excel 2003.
I also have a SSIS written in SQL 2005 that will import value from
this excel sheet, i am just wondering how could i make the SSIS job
capture the displayed value 1 (or round it to 1 during data
extraction) instead of '0.99999999999999978' as we are expecting an
Interger instead of a double...

TOTO
 
Back
Top