Forum Home
Press F1
Thread ID: 77817	2007-03-23 21:50:00	Zero should not equal -4.7185E-16	porkster (6331)	Press F1

Post ID	Timestamp	Content	User
535339	2007-03-23 21:50:00	Hi, I'm a bit stumped with a problem I have in Excel. I do a simple subtraction equation that should equal zero but doesn't! Try this in Excel Cell A1 7.38 A2 7.15 A3 0.23 A4 =A1-A2-A3 Should equal zero right? It doesn't, it equals -4.7185E-16 Ive try it with several different versions of windows and Excel but they all seem to do it. I discovered this doing money calculations (formatted for currency so the -4.7185E-16 shows as zero) and testing for zero (to check manual entries). It drove me batty and had me running around in circles for a while. Try this Cell A1 7.38 A2 7.15 A3 0.23 A4 =A1-A2 A5 =A4-A4 Just plain weird. I hope my bank doesn't use Excel. I've tried looking for others/information with the same problem on the net but there is just to many questions on Excel out there. Does anyone know if Microsoft have put out any info on this, and how do I report it to Microsoft? (but surely they know of this fault). Does Excel 2007 do this? (I can't afford to upgrade again!) Thanks in advance! The porkster.	porkster (6331)
535340	2007-03-23 22:07:00	Format the cell that is using the calculations as "currency", or at least "number"	bob_doe_nz (92)
535341	2007-03-23 22:07:00	Excel 2007 gives the same outcome.	godfather (25)
535342	2007-03-23 22:17:00	Excel 2007 gives the same outcome. So it's possible that Microsoft doesn't know or they would have fixed it....maybe?	porkster (6331)
535343	2007-03-23 22:32:00	The issue is not new nor really a bug, its the way numbers have to be translated to binary (and back). Read it here. www.cpearson.com "The second problem arises from the fact that a computer, any computer, cannot store most fractional numbers with total accuracy. Computers, in general, use the IEEE (Institute Of Electrical And Electronic Engineers) standard for floating point numbers. This standard provides a way to store fractional numbers in the limited space of an 8-byte number. Of course, for most numbers, some approximation must be made. "	godfather (25)
535344	2007-03-23 22:37:00	Excel has some issues with floating point calculations, but this usually impacts very small or very large numbers. See this article at MSKB (support.microsoft.com). If you use =A1-(A2+A3) you get zero. If you are getting anomalies like this I suggest using the Round function to round the value to the number of decimals you wish to calculate to.	Parry (5696)
535345	2007-03-24 00:19:00	See this: www.imagef1.net.nz Near enough to zero. Your average calculator won't show that many places.	pctek (84)
535346	2007-03-24 01:35:00	The problem is nothing to do with Excel. When represented in a computer memory all numbers must take up a fixed amount of space. For "floating point" numbers, the type used here, this is either 32 bits (4 bytes) for single precision or 64 bits (8 bytes) for double precision. Many numbers that you can write very compactly in decimal (base 10) notation require many more (possibly uncountable) decimal places in binary notation. As a result they can't be stored exactly. This is usually not a problem but these inaccuracies add up when you perform arithmetic on these numbers. Usually these differences would be below the rounding threshold for display but around zero the computer will attempt to display what is left over by using a very small exponent (10^-16 in your case). I don't know how in Excel but the easiest way to stop this from displaying would be to prevent it from displaying scientific notation, probably through the cell format options.	TGoddard (7263)
535347	2007-03-24 02:10:00	... Just plain weird. I hope my bank doesn't use Excel.Of course they do. Those "zero" amounts add up to real money if you have enough of them. :D It's not a "display" problem. The naughty little bits don't display; they just lurk there and foul up the (logical) result of comparison to zero which might never, ever, return a true value, so an iteration can't terminate. As has been said, it's a well known feature of floating point arithmetic. ;) One of the standard tricks used to be to use if abs(value) < epsilon... , where epsilon is a "small value". Once I had this problem with a Pascal programme where even "lessthan epsilon" didn't work properly on reals, so I just multiplied all my values by 10000 and made them long integers. Quick and dirty fixes like that often work. :cool:	Graham L (2)
535348	2007-03-24 02:42:00	Of course they do. Those "zero" amounts add up to real money if you have enough of them. :D Thats what banks use them for. It makes mucho profit from roundings like these. Especially on interest. They to the nearest whole number (down of course) and put it in your account. The rest goes straight into there slush fund (read profit)	Big John (551)
1 2 3