Excel XML worksheet export has unprecise numbers - Microsoft Community
i use "excel 2004 worksheet" format export data further processing (using xslt or php).
however, noticed excel 2016 mac export strange, unprecise numbers.
if create spreadsheet (precise) numbers so:
| 100,00 |
| 100,10 |
| 100,20 |
| 100,30 |
| 100,40 |
| 100,50 |
| 100,60 |
| 100,70 |
| 100,80 |
| 100,90 |
| 68,40 |
all numbers except last 1 exported correctly:
<table ss:expandedcolumncount="1" ss:expandedrowcount="11" x:fullcolumns="1"
x:fullrows="1" ss:defaultrowheight="16">
<row>
<cell ss:styleid="s62"><data ss:type="number">100</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.1</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.2</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.3</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.4</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.5</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.6</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.7</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.8</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">100.9</data></cell>
</row>
<row>
<cell ss:styleid="s62"><data ss:type="number">68.400000000000006</data></cell>
</row>
</table>
due tests there numbers exported incorrectly, while others exported correctly.
68,40 number test with.
unfortunately, removed excel 2011 mac, can't compare behaviour.
michael wrote:
thanks lot! understand technical background now.
you're welcome. meant add following, shows exact decimal approximation of other values decimal fractions.
note, example, 100.2 rounds 100.20000000000000, why 100.2 in xml file.
the surprises 100.4 , 100.9, might expect to round 100.40000000000001 , 100.90000000000001 in xml file.
i suspect reason not has "banker's rounding" (round half even) v. "normal" rounding (round half away zero). educated guess @ point. , tmi.
Office / Excel / Mac / Office 2016 for Mac
Comments
Post a Comment