 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007ROUND problem excell 2003 70 – 6 9,3 results in 0,700000000000003 00
We have the following problem with excel 2003 When subtracting something from 70 we get round of problems Example: 70 – 69,3 results in 0,700000000000003000000000000000 Is this a known issue? Regards, Joost Keijer Hi Joost, <Is this a known issue?> Yes. Look here: http://support.microsoft.com/kb/78113  SignatureKind regards, Niek Otten Microsoft MVP - Excel | We have the following problem with excel 2003 | [quoted text clipped - 6 lines] | | Joost Keijer This is just normal rounding error. Occurs in all versions of Excel. Use: =ROUND(70-69.3,1) if greater precision is needed SignatureGary''s Student - gsnu200750 > We have the following problem with excel 2003 > [quoted text clipped - 6 lines] > > Joost Keijer I have the same problem while using Excel2007. ROUND(1.2 - (70-69.3)) gives 0 and should be 1..... > We have the following problem with excel 2003 > [quoted text clipped - 6 lines] > > Joost Keijer Hi Hans ROUND(1.2 - (70-69.3),0) correctly gives 0 ROUND(1.2 - (70-69.3),1) gives 0.5 ROUNDUP(1.2 - (70-69.3),0) gives 1 SignatureRegards Roger Govier >I have the same problem while using Excel2007. > [quoted text clipped - 10 lines] >> >> Joost Keijer Hi Roger, Thank you for efforts in helping solving this strange "phenomenon". I understood the function ROUND works like this for numbers with one decimal: 0.4=>0, 0.5=>1, 0.6=>1, 1.5=> 2, 2.5=>3 etc. So ROUND (0.5;0) should give 1 70-69.3 should give 0.7 and not 0.7000000000000003 And 1.2 - (70 - 69.3) should give 0.5 exactly and not 0.499999999999997 So ROUND ((1.2 - (70-69.3));0) should give 1? > Hi Hans > [quoted text clipped - 16 lines] > >> > >> Joost Keijer You need to learn how computer store numbers. See for example: INFO: Visual Basic and Arithmetic Precision http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/s upport/kb/articles/Q279/7/55.ASP&NoWebContent=1 (Complete) Tutorial to Understand IEEE Floating-Point Errors http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/s upport/kb/articles/Q42/9/80.ASP&NoWebContent=1 http://support.microsoft.com/kb/78113/en-us What Every Computer Scientist Should Know About Floating Point http://docs.sun.com/source/806-3568/ncg_goldberg.html http://www.cpearson.com/excel/rounding.htm best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Hi Roger, > [quoted text clipped - 29 lines] >> >> >> >> Joost Keijer I am just a simple Excel user. Do I need to learn how the computer stores numbers? Maybe 30 years or more ago. I expected Excel (ie. the ROUND-function) to do a proper job in handling simple decimal numbers (1 decimal accuracy). I will try to find a work around in this case, but I have lost absolute trust in future calculations of Excel...... > You need to learn how computer store numbers. See for example: > INFO: Visual Basic and Arithmetic Precision [quoted text clipped - 39 lines] > >> >> > >> >> Joost Keijer Only if you want to understand why you get small non-zero numbers when you expect exactly zero. ROUND always works. best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I am just a simple Excel user. Do I need to learn how the computer stores > numbers? [quoted text clipped - 49 lines] >> >> >> >> >> >> Joost Keijer A simple way to think about it is to use the documented limit of 15 figures. The problem then becomes 70,0000000000000?? -69,3000000000000?? ------------------------- 0,7000000000000?? which is consistent with Excel's reported result of 0,700000000000003 Understanding Bernard's references are not required to use Excel, but they will make it clear what is actually going on. The math is exactly correct, but Excel is working with numbers that are slightly different than you expect. Also note that this is a finite precision issue (common to all software except symbolic manipulators) compounded by decimal/binary conversions (common to almost all software), and not just an Excel issue. Most terminating decimal fractions (including ,3 and ,7) have no exact binary representation, and hence must be approximated. When you do math with approximate inputs, that the output is only approximate should be no surprise. The exact value of the approximation to 69.3 is 69,2999999999999971578290569595992565155029296875 which when subtracted from 70, correctly yields 0,7000000000000028421709430404007434844970703125 Excel is working with and returning these exact values, but (as documented) will never display more than 15 digits of any value. If you ask for more than 15 digits, you will get meaningless trailing zeros. You can see more using my VBA conversion functions http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/9f83ca3d ea38e501/6efb95785d1eaff5 Jerry > I am just a simple Excel user. Do I need to learn how the computer stores > numbers? [quoted text clipped - 4 lines] > in this case, but I have lost absolute trust in future calculations of > Excel...... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.