Need help understanding Number rouding Excel does

Originally Posted by :) Sixthsense :)

Not sure which one is second row and first row

It will be helpful if you show it in excel

The columns are delimited with '|' pipe character and in the first data list they are delimited with tab character.

Originally Posted by MrShorty

Disclaimer: I have never used the Python programming language.

That said, shouldn't it be as easy as using Python's ROUND() method? http://www.tutorialspoint.com/python/number_round.htm

The rounding is not so straight forward,
1. When I paste "2.1099999999999999" into excel cell it is displayed as 2.11 and the formula section shows '2.10999999999999' which is 14 digits post decimal
2. When I paste "6.2409999999999997" into excel cell it is displayed as 6.241 and the formula section shows '6.24099999999999' which is 14 digits post decimal

The number of significant digits post decimal keeps changing, if that would have been consistent I can use the round function to that number of digits.

Thanks

Apologies if my post was un-clear.

As mentioned in my earlier post, I am designing Python code that would dump the excel contents into CSV file.

When the values are dumped into CSV, I want to have the values that appears in the cell to be dumped rather than the real values of double precession's which is actually stored.

Suppose an user types 4.31 in the cell formatted as General, it would get stored as double precision whose real values is '4.3098989898989801' and if '9.3478' is entered it would be stored as '9.3477999999999994'.

My Python code when run against the above excel containing 4.31 and 9.3478 fetches the below values
4.3098989898989801
9.3477999999999994

The excel files are user created and I don't have any control over them, the users would run this Python code when they want convert them into CSV files (this is on Unix environment)

From 4.3098989898989801,9.3477999999999994 I have to get back '4.31' and '9.3478' which is exactly what the user entered any other values if fetched would be called as a bug by the user.

Why Rounding would not help:

4.31 has two digits of precision
9.3478 has 4 digits of precision and if rounded to 2 digits of precision it would end up as 9.35 which is not what the user entered.

Since the users can enter data with varying digits of precision, I can't apply the round function.

Having said this, If '4.31' is entered and its stored as '4.3098989898989801', if the excel is closed and reopened and it displays '4.31' it means that excel is applying some formula or has remembered the exact precision of the user entered value so it was able to get back 4.31 from 4.3098989898989801 and 9.3478 from 9.3477999999999994

If I enter 4.3098989898989801 in the excel cell (format as General) it shows me 4.31 instead of 4.3098989898989801 which means it has rounded to two decimal digits and when I enter 9.3477999999999994 it shows 9.3478 and this time it has rounded to 4 digits of precision.

This means that excel dealing with the inputs 4.3098989898989801,9.3477999999999994 in some fashion to get 4.31 and 9.3478 ? I need to do the same in my Python code to get back what the user actually entered.

Hope I have made my issue clear.

Thanks