Access or Excel Number Problem (Access 2000/Excel XP)

Topic

New Reply

I have run into a problem with number differences, with data imported from Access 2000 into Excel XP. Initially I had thought it was an Excel problem, but one of my co-workers thinks he remembers seeing something about Access causing this kind of problem. While I am describing the issue in Excel, the data is imported from Access.

In col. A & B have percents (A2+ B2 =100%, i.e. the totality of the data) from one set of numbers, and Col. C & D have percents (C2 + D2=100%, i.e. the totality of the data) .

In col. E I subtract A2-C2 and get this number: 2.4112898185579400%

In col. F I subtract B2-D2 and get this number: -2.4112898185579500%

Technically they should be identical (except for minus sign – thus added = 0). In 739 rows of comparisons I get about 1/3 of them where they are identical. The others are off by this decimal or sometimes a little more. For most purposes such discrepancies would not be a problem. However, when using IF statements or any kind Match function, these differences mean that the two columns (E and F) will not be the same, hence the formula will not be “accurate.”

Is there a known problem with Access and number errors like this? Can anything be done to correct?

There does not seem to be a search feature (although the FAQs describes one, there is none on the menubar) for this web site. And I haven’t found anything about this problem in other searching.

Any helps/suggestions/directions would be greatly appreciated.

Reply | Quote

Replies

To our regret, the search facility has had to be suspended temporarily. See post 250092

This kind of problem arises because numbers are stored with only finite precision. Because of this finite precision, small rounding errors occur. In the decimal number system we humans use, the number 1/3 written as a decimal number is 0.333333333…. – the 3’s go on forever. If I stop after 5 decimals (0.33333), I make a small error; if I stop after 10 decimals, the error will be smaller, but it will still be an error. Something similar holds for the way computers store numbers.

Excel is programmed to hide these little discrepancies from us as much as possible when we enter data. But when data are imported from outside, they are imported “as is”.

Temporarily switch Excel to precision as displayed (Tools | Options…, Calculate tab). Then format the columns to, say, 10 decimals. The errors occur in the 14th decimal, so the numbers should look OK now. Then uncheck precision as displayed (it’s probably not a feature you want “on” permanently).

Reply | Quote

Thanks, Hans. Frustrating to deal with, but certainly well worth knowing the work around – at least I don’t have to require precision beyond 10 decimals.

Thanks, again.

Reply | Quote