Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    To our regret, the search facility has had to be suspended temporarily. See <post#=250092>post 250092</post#>

    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).

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. [img]/forums/images/smilies/smile.gif[/img]

    Thanks, again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •