Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    10 decimal place mystery (windows xp, xl97, sp2)

    Appreciate straight forward help please,
    I've got cash books which, in summary, summate sets of figures in and out of company. At the end of the sheet the two resulting figures are compared to make sure they are equal. IF the figures are not equal, then by using conditional format, the cell backgrounds are changed to red and an error message in the adjacent cell is formatted bold and black. All entries (numerical) are to 2dp, i.e. currency, although the cells are not set to currency format. (if you are familiar with accountancy sheets, most of the main data figures are not showing currency until they total up).
    For some strange reason, and I can replicate it time after time, when a particular value is entered, the two results figures appear equal, but the conditional formatting kicks in and there is an error message displayed on the sheet (its not a dialogue error message box).
    The option to use precision as displayed is not selected (by choice). It appears that the error is occurring because as soon as I enter a value of 217.96 (this figure is significant as you will see), the result changes to indicate a new sum of say 120256.71000000000001000 or some such number.
    If, however, two values are entered in the same column of 217.95 and 0.01 (the equivalent overall value) then no error occurs.
    The problem is not how to resolve the error (I changed the conditional format so that only errors greater than .01 change the format) but why is it happening? Is it a specific Excel bug which I have not yet heard of? Very worrying when we are talking about larger figures and I now find myself worrying about how much time is being wasted searching for errant figures which are not actually in error.

    Thanks in advance

    Alan
    Cheshire
    UK

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 10 decimal place mystery (windows xp, xl97, sp2)

    This is caused by the fact that a computer is a binairy machine and uses binairy numbers to do its tasks. So it has to convert any decimal number into binairies, which may lead to (small) rounding errors. Typically, you should only see an error in the last (of 15) digit of your number.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 10 decimal place mystery (windows xp, xl97, sp2)

    Jan gave you an explanation. A workaround is NOT to compare (for example):
    =A1<> A2
    in cond formatting but something like:
    =round(A1,5)<>Round(A2,5)

    Or choose a different number of decimals.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 10 decimal place mystery (windows xp, xl97, sp2)

    Thanks for the prompt replies. The solution given will resolve the problem and I appreciate another fresh pair of eyes to be able to help it along.
    As with most of the excel problems the words "cat", "skin" and "many ways" spring to mind.
    Using the NOT compare was something I hadn't tried and the rounding will indeed cut out the error problem, but it doesn't exactly explain why it throws a googley.
    I have resolved myself to the fact that in future I should really only be working in whole integers and save the decimals for the final summation.
    Once again many thanks.
    Alan
    Cheshire
    UK

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 10 decimal place mystery (windows xp, xl97, sp2)

    I keep all the decimals in the calculations until the end and if (need be) you round it then to the "desired" (or the significant) number of figures. If I need a comparison where rouning errors could affect it, I do as I had suggested.

    Steve

Posting Permissions

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