Excel saves values incorrectly to XML

[Sniipe]

I have a column in excel that is a percentage. When I save 6% it saves to the XML as

<Cell><Data ss:Type="Number">0.06</Data></Cell>

BUT when I save 7% it saves to the XML as

<Cell><Data ss:Type="Number">7.0000000000000007E-2</Data></Cell>

I understand why Excel does this, but how do I go about saving 7.0000000000000007E-2 as 7 in the XML?

 

[xlbo]

are you sure it is exactly 7?

How is the data entered into excel? If it is a result of a formula then more than likely the real number is 7.000000001 or similar. If you are simply typing 7 into a cell then I don't know....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

[tt]<Cell><Data ss:Type="Number">7.000000000000000[red]7[/red]E-2</Data></Cell>

[/tt]
because the value is NOT EXACTLY .07!!!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sniipe]

The value is exactly 7. I type in 7.

Here is how you get the same results as I did. I tried this on excel 2007. I have also tried it on earlier versions of excel, but don't get the same results.

Open excel.
Format a cell to be percentage with 2 decimal places.
Enter in 7
Save as excel xml Spreadsheet 2003
When you open that spreadsheet 7 appears incorrectly. If you enter in other values (besides the ones I've listed below they come out correctly)



Other values that don't work are:
7 - 7.0000000000000007E-2
14 - 0.14000000000000001
28 - 0.28000000000000003
29 - 0.28999999999999998
55 - 0.55000000000000004
56 - 0.56000000000000005
57 - 0.56999999999999995
58 - 0.57999999999999996

But in the programming behind, 7 is the only one that has the E-2 in it.

reference link:

http://www.cpearson.com/excel/rounding.htm

 

[xlbo]

The E-2 is just scientific notation for displaying tiny fractions of numbers...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question