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

boxarox

New Member
Joined
Jun 20, 2012
Messages
5
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?
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 20, 2012
Messages
5
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

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,983
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,983
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top