Decimal Place Rounding Error

    Hi everyone,


    I have 2578 in cell A1 and 2557.9 in cell B1.


    In cell C1 I have =A1-B1


    The answer I get is 20.1. But if I take the decimal place to 20 by changing the number format I get 20.09999999999990000000


    Why is the answer not 20.10000000000000000000 ?????


    Thanks in advance.

    Re: Decimal Place Rounding Error


    Have done some more thorough searching and found this mumbo-jumbo from microsoft. Which I think it has something to do with. Its hurts my small brain reading it though.


    http://support.microsoft.com/kb/78113


    and


    http://support.microsoft.com/kb/214118/EN-US


    All my cells are formatted to 2 decimal places, I only noticed the problem because when the values are loaded into a textbox it displays the answer to 13 decimal places.


    Im still hoping some wiz here will explain the problem in english unlike microsoft!

    Re: Decimal Place Rounding Error


    Howdy. There is a difference between the stored value and the displayed value. On top of that, a number has limits (utlimately 15 characters), whereas a number that is really text can have as many characters as normal text cells (1,024... but I have seen where the number of characters in a cell approaches 32,000).


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

    Re: Decimal Place Rounding Error



    when you use the number format please note it is only the display which is rounded off...the underlying value remains the same


    to match the display with the underlying value use one of many rounding off functions like ROUND,ROUNDUP,ROUNDDOWN,TRUNC,FLOOR,CEILING,MROUND etc

    Re: Decimal Place Rounding Error


    Pangolin and shades, Hi there..


    I am aware that when you use the number format it is only the display which is rounded off...the underlying value remains the same.


    What I want to know is why excel makes the error in calculation... ie the underlying value is wrong. Am I missing something?


    Thanks for your replys.

    Re: Decimal Place Rounding Error


    then check the values in cell A1 and B1 ...perhaps the actual value is not the same as being displayed

    Re: Decimal Place Rounding Error


    No they are correct, im 99% sure it is a calculation error.


    I started a completely new worksheet and put the values in. Then I increased the decimal place to 20 and I get that weird number.


    Try it you will see.


    I cant understand the articles from microsoft I posted above but im sure it has something to do with it.

    Re: Decimal Place Rounding Error


    The numbers in Excel are held as floating-point values. These values are not precise representations of decimal quantities, because they are held as binary strings in a particular format. When arithmetic is done, small errors occur, but for most practical purposes these are covered by rounding. But there are some Excel functions using compares, notably LOOKUP-type functions, that can fail.

    Re: Decimal Place Rounding Error


    Cheers Cringe,


    Appreciate the explanation, and take it on board as best as I can.


    Its all a bit much to be honest but I will remember it if I ever have such problems with lookup as you mentioned.


    Also if anyone happened to have the same problem with the long display in the textboxes, I got around this by clicking precision as displayed textbox under the general tab in options menu. Im sure there's other solutions tho, like formating the textbox.


    Thanks again all.

    Re: Decimal Place Rounding Error


    Here's another way to look at it. In base 10, 1/3 is a repeating decimal: 0.333333.... It cannot be exactly represented with any finite number of digits.


    In binary, 0.1 is a repeating decimal: 0.0001100110011001... (or 0.199999... in hex). There is no exact representation in binary, and IEEE singles and doubles do the best they can with their 23- or 52-bit fractions.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login