Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rounding limitation? (XL97 sr2 on Win2K/NT4)

    I know Excel can be a little limited at times but...
    Enter =(2.03-2)/0.2 into a cell and you get 0.15, in the cell underneath enter 0.15.

    OK so now you have two cells that hold identical values right? Apparently not!

    In the cells next to your two numbers ask Excel to round your values to 1dp using =Round(cell ref),1). You will get answers of 0.1 and 0.2.

    If you reformat the cells to 15 dps then you see why, Excel's calculation engine comes up with 1.49999999999999 for the answer to the simple sum.

    Deep sigh, is there a fix/workaround for this irritation?

    stuck

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

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    These things happen because not all numbers can be stored exactly.

    Some numbers can be represented exactly in the decimal system, for instance, 2/5 = 0.4. But other numbers can only be approximated to a finite accuracy, for example 1/3 = 0.3333333.... We have to stop somewhere, so there is a rounding error.

    The same holds for the binary number system used by computers and computer programs. For instance, 1/2 = 0.1 binary (exactly), but 2/5 = 0.01100110011001100... Again, we have to stop somewhere, so there is a rounding error.

    Excel stores numbers with a precision of (about) 15 decimal digits. This is enough for most practical purposes, but as you have found out, there are situations where the rounding error is noticeable.

    There is no real fix for this, since it is impossible to store numbers to infinite precision. You can set Excel to store numbers with the displayed precision (Tools | Options..., Calculation tab, Precision as displayed check box), but this may have unexpected side effects.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    "we have to stop somewhere". Agreed, so no need to answer this one <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> ...

    So how come my hand held calculator can cope with the sum and give me exactly 0.15 as the answer? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Surely it too must deal with binary rounding and if the technology existed to handle the problem 20 years ago when I bought my calculator how come <img src=/S/ranton.gif border=0 alt=ranton width=66 height=37> ...fill in your favourite MS <img src=/S/bash.gif border=0 alt=bash width=35 height=39>ing text... <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37>

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    stuck

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

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    This is because calculator manufacturers built in some kind of fudging algorithm. The earliest calculators did show rounding errors much more than Excel does nowadays. This was unacceptable to the general public, so a rounding mechanism was built in. As with the "Precision as displayed" option in Excel, this may have undesired side effects in some circumstances, but that is less likely to cause problems for users of handheld calculators than in a complicated spreadsheet.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    Well, I thought you said that Excel also displayed your answer as .15 <img src=/S/yep.gif border=0 alt=yep width=15 height=15> .

    Change your formula to the following and it should fix your problem:

    <pre>=ROUND((2.03-2)/0.2,2)
    </pre>

    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    That is not an option nor is working with 'precision as displayed'. This is just a trivial example. In my actual sheet the Round function is part of a more complex formula that is replicated down several columns and 1 dp is required.

    As I discovered and as Hans explained, it just so happens that on rare occasions the rounding fails. I didn't really expect a fix I just wondered how other people had tackled this issue.

    I can think of a possible kludge involving something like "if the right 10 characters are 9 then Roundup otherwise Round" but I'm not sure it's worth the effort as in my case the answer of 0.1, rather than 0.2 is erring on the cautious side so I can live with it.

    (not really)stuck

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    If the If statement is an option, why isn't the Round an option? If your formula is in A1, try these:

    <pre>=ROUND(ROUND(A1,2),1)
    =ROUND(A1+0.00000000001,1)
    </pre>


    Checking the right 10 characters for 9s is not going to work. The rounding can make several of the last digits not equal to 9s.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding limitation? (XL97 sr2 on Win2K/NT4)

    AHH! (I wish there was a light bulb smilie) rounding to two 2dp then rounding again to 1dp.

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23> Why didn't I think of that? What is is to be an WMVP.

    Thanks!

    (definitely not)stuck

Posting Permissions

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