Excel adds 7 zeros and a 1 ater the 3rd decimal. Whats up with that?

[boxarox]

I've a large spreadsheet that I used to compile bid codes which I import into another application. Column A contains 4 or 5 digit numbers carried out to 3 decimal places. A few columns over I enter data then a few cells after that I write a =concatenate formula to compile all the cells where I entered the data.

So in column A I start with the number 5000.001, then Drag and Copy down column A to 5000.999. then I copy paste as values the results of the concatenate formula into column B. At some locations in the worksheet, Excel adds 00000001, so in row 1361 5000.026 becomes 5000.02600000001.at row 1409 5000.074 becomes 5000.07400000002. (that's 48 Rows difference)

When I try to reformat the column as numbers, with 3 decimal places, several the numbers then become the same. I may have several rows that say 5000.050.

Under options-advanced-when calculating this workbook- I have the have "precision as displayed"checkbox checked.

I opened a 2nd instance of Excel, and copied the 1st 12 columns to a new file. The same error occurred, but when I checked the "precision as displayed checkbox", the values appeared correctly.

Does anyone have an idea why this occurs and how I can prevent it from reoccurring in my newly created worksheet?

 

[Drrellik]

if I understood you correctly you are using =Concatenate( , ) to join to cells. you do understand that if cell a1 is 500.001 and cells b1 is 500.002 and you use Concatenate then you will get 500.001500.002 this function simple joins the two cells as text and does not add as a number. so when you say you checked the values these are not values in the sense 'Number this formula looks at it as text.
hope this helps

~DR

 

[boxarox]

The values included in the Concatenate string reside in columns D through G, so Column A is not included in the string. The concatenate formula resides in column I.

I then copy column I and "paste as values" into column B, adjacent to the numbers created by my copy/drag. So what I end up with is a numeric value in column A and a text description in column B. See below:

A B
5000.026 W8x24 Floor Beam
5000.027 W8x27 Floor Beam

Then suddenly:

5000.02800000001 W8X31 Floor beam

This will continue for 48 rows, then the 1 becomes a 2.

I then copy columns A and B into another database in another program.

But it doesn't matter whether column B has anything in it or not, the 7 zeros+ 1 digit gets added to the values in column A seemingly at random regardless of any contents in column B. I only included the information about the concatenate formula, as ancillary information. In case there is some prosaic way that its inclusion might cause the problems I am experiencing in column A

I see a correlation between the 8 figures added, and 8 bits to the byte and the fact that each hexadecimal represents 4 bits and the 48 rows between the change in integer. But believe me, I'm no programmer I don't even know if this makes any sense at all given the context.


By the way-- I love your quote.

 

[Drrellik]

I thank you for the Nod' on my quote, and as for your issue I am stumped at the moment, but I will do some research, here is a link to Microsoft about Floating point precision, but it may not pertain it is just a wag at this point, any chance you can try this on a different computer, same version of Excel or send a small sample to me @ daross@sc.rr.com and see if I can see it first hand.

Anyway thanks again and hope you get it worked out.

~DR

 

[joeu2004]

So in column A I start with the number 5000.001, then Drag and Copy down column A to 5000.999. then I copy paste as values the results of the concatenate formula into column B. At some locations in the worksheet, Excel adds 00000001, so in row 1361 5000.026 becomes 5000.02600000001.at row 1409 5000.074 becomes 5000.07400000002.

Concatenate has nothing to do with it.

First, I assume you mean that you start with 5000.001 and 5000.002, select the two cells, and drag down. Then, yes, what should be 5000.025 is 5000.02500000001. And the numbers continue in that manner until what should be 5000.074 is 5000.07400000002.

The reason is indeed due to binary arithmetic. The problem is: most non-integers cannot be represented exactly. Consequently, 5000.001 and 5000.002 are not exactly those values [1].

We cannot see the infinitesimal difference because Excel refuses to format more than 15 significant digits -- 11 decimal places, in this case. But note that 5000.002 - 5000.001 is 0.00100000000020373, not exactly 0.001.

In general, the remedy is: when you expect or require accuracy to n decimal places, explicitly round to n decimal places.

In this case, put 5000.001 into A1, put the formula =ROUND(A1+0.001,3) into A2, and copy A2 down the column.


-----
[1] 5000.001 is exactly 5000.00100000000,020372681319713592529296875.
5000.002 is exactly 5000.00200000000,04074536263942718505859375.

 

[joeu2004]

PS....

when I checked the "precision as displayed checkbox", the values appeared correctly.

Precision As Displayed (PAD) remedies the problem when the cells are formatted with 3 decimal places because PAD implicitly rounds the cell result to that number of decimal places.

However, setting PAD is a bad idea, IMHO.

If you enter constants with more decimal places than the cell format, PAD permanently rounds those constants. And PAD affects all cells in all worksheets. So when you set PAD in an existing Excel file, it might have irreversibly changed constants that you are not aware of.

Finally, PAD does not remedy all binary arithmetic problems. For example, =IF(5000.002-5000.001=0.001,TRUE) returns FALSE(!), even when PAD is set. The reason is: PAD only affects the final cell result, not intermediate calculations.