PDA

View Full Version : Zero should not equal -4.7185E-16

porkster
24-03-2007, 09:50 AM
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!)

The porkster.

bob_doe_nz
24-03-2007, 10:07 AM
Format the cell that is using the calculations as "currency", or at least "number"

godfather
24-03-2007, 10:07 AM
Excel 2007 gives the same outcome.

porkster
24-03-2007, 10:17 AM
Excel 2007 gives the same outcome.

So it's possible that Microsoft doesn't know or they would have fixed it....maybe?

godfather
24-03-2007, 10:32 AM
The issue is not new nor really a bug, its the way numbers have to be translated to binary (and back).

http://www.cpearson.com/Excel/rounding.htm
"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. "

Parry
24-03-2007, 10:37 AM
Excel has some issues with floating point calculations, but this usually impacts very small or very large numbers. See this article at MSKB (http://support.microsoft.com/kb/78113).

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.

pctek
24-03-2007, 12:19 PM
See this:

http://www.imagef1.net.nz/files/Image1.jpg

Near enough to zero. Your average calculator won't show that many places.

TGoddard
24-03-2007, 01:35 PM
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.

Graham L
24-03-2007, 02:10 PM
... 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:

Big John
24-03-2007, 02:42 PM
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)

Thomas01
25-03-2007, 04:03 PM
Dead Easy to fix. Well I think so. I never use Excel. Much prefer MSWorks spreadsheet - faster simpler and although it has not got the ability of Excel it can actually do things Excel cannot (that surprises people!).
I tried your first problem and got the same result.
So I just selected all the used cells - went into format - changed from 'general' to 'fixed' and made it 2 decimal places. No problem - it worked fine. Took all of about 30 seconds to try and then correct.
Which is why I use MSWorks!!
Tom

pctek
25-03-2007, 04:41 PM
Much prefer MSWorks spreadsheet - although it has not got the ability of Excel it can actually do things Excel cannot
So I just made it 2 decimal places.

You do that in Excel and it gives the same result too.

I used Works Spreadsheet yesterday, I was recreating a sheet I'd used myself on Excel for a lady and I found out that it sure doesn't have the ability of Excel. It sucks.

Thomas01
25-03-2007, 06:33 PM
You do that in Excel and it gives the same result too.

I used Works Spreadsheet yesterday, I was recreating a sheet I'd used myself on Excel for a lady and I found out that it sure doesn't have the ability of Excel. It sucks.

Yes I thought Excel would be similar - in fact I did check and found I had at some time or other set my format in Excel to two decimal places so the problem didn't arise.
But your last comment was uncalled for. I don't criticise Excel because it took me twice as long to find the format command - Excel is a brilliant spreadsheet. So is MSWorks and for the average person is quite ample. In the old days when I was still cycling I used the bike to go to the dairy - took me far less time than getting the car out of the garage and messing about with roundabouts, parking etc. The bike could not do what the car could but it was still excellent for its purpose. I would never dream of calling it silly names.
Funny enough I also still use ASEASYAS - yes the old DOS spreadsheet. The reason is that it can do things neither MSWorks or Excel can cope with.
I use it for a particular maths function - nobody has been able to come up with a way of doing it with a Microsoft product.
That is probably why it still sells.
Tom

dolby digital
26-03-2007, 10:55 AM
Although slightly off topic, I will throw it into the ring anyway. I once did some work for a quy who had degrees in stats and he used Gnumeric (Linux)... and the reason he used Gnumeric... because the stats functions were much better than those in Excel :eek:

Of course, it would have the same problems with floating point arithmetic.

pico
26-03-2007, 08:19 PM
Hey all,

Not sure if you noticed but you can write the equation so it does equal zero without rounding it:

=A1-(A2+A3)

and

=A1-A3-A2

both give me zero while the original equation

=A1-A2-A3

Gives me the tiny error number quoted. Must have something to do with the way that Excel carries out the operations. I should check if it happens in code too! (goes away for a few seconds to check)

Yup when you do the arimetic in code it comes up with the same error try:

Debug.Print cstr(7.38-7.15-0.23)

In the Immediate window in Visual Basic. Gives the same answer. Interesting.

TideMan
26-03-2007, 09:13 PM
This rounding thing is all so boring and old hat.........
We learnt about it back in 1967 in Engineering Computing at U of Canty when we punched a Hollerith card for each line of command for the IBM 360 mainframe. We were taught by a professor called Bruce Moon. At the time, decimal currency was about to be introduced and he desperately tried to get the base changed from decimal to duo-decimal (12). His reason was that 10 is such an awkward number. Its only factors are 5 and 2, whereas 12 is a beautiful number. Boxes are packed in dozens because 3 x 4 works so well in cartons, etc, etc. He lost of course.

Of much more importance in Excel is the bug in its date algorithm.
Type: 28/2/1900 in A1 and in A2 type =A1+1
What do you get? 29/2/1900, right?
But it's wrong. 29 Feb only occurs on centuries which are divisable by 4.

Who cares?
Well don't try forecasting tides using Excel. The base date by convention is 1/1/1900, but you'll be a day out if you use Excel.

somebody
26-03-2007, 09:24 PM
Just plain weird. I hope my bank doesn't use Excel.

Well after 2092 TRILLION calculations, you will lose 1 cent, so I think you'll be safe.

porkster
26-03-2007, 11:16 PM
Well after 2092 TRILLION calculations, you will lose 1 cent, so I think you'll be safe.

Darn, This will put a kink in my plan to become a multi-trillionair.

For those who say that fixing it to 2 decimal places fixes it you are incorrect. the test for zero still fails. (the rounding suggestion fixes it).

I should have guessed the problem was the floating point calculation, I use to teach programming a while back although the CPU we used could calculate using BCD and therefore didn't suffer with this problem.

Thanks for you help.

Thomas01
27-03-2007, 10:19 AM
For those who say that fixing it to 2 decimal places fixes it you are incorrect. the test for zero still fails. (the rounding suggestion fixes it).

I should have guessed the problem was the floating point calculation, I use to teach programming a while back although the CPU we used could calculate using BCD and therefore didn't suffer with this problem.

Thanks for you help.

Hmmm!
Now you have got me puzzled. How can you say the fixing to 2 decimal places is incorrect when it clearly isn't. It does fix it.
So obviously the test for 0 is yet another problem.
Perhaps you could expand a little. I think I am beginning to get what you are saying but I have forgotten a lot of my maths theory. Perhaps you could give us an example of what you mean.
Tom

Graham L
27-03-2007, 05:24 PM
It comes in the (large) subtopic "numerical methods", Thomas. The setting to two decimal places will not (in general) solve the problem. That only adjusts what is displayed (usually rounded to not look silly). It does not affect the internally stored numbers. What is stored and used for calculations and comparisons will be the full floating point representation. So a calculated value of 100.20 might actually be stored as ".1001999999999999999E+03". A calculation which has a result displayed as "0.00" might actually give ".000000000000000012E+0" or even "-4.7185E-16". In almost all calculations this doesn't matter: it's close enough. But in a logical comparison, all bits matter. Unless a value has all the bits off (because of a lucky combination of data and operations) a test for zero will fail. This has been known to cause infinite loops, strange results, ridiculous accounts, and a lot of bad language.

Agent_24
27-03-2007, 08:44 PM
Excel is not good enough. Quattro Pro is better. It has 1,000,000 rows while Excel only has 65,536.

HA!

TGoddard
27-03-2007, 11:15 PM
If you use that many rows you should be shot. Spreadsheets are good for displaying data including basic calculations. They are not suitable for large-scale data storage. That's what a database is for.

Agent_24
27-03-2007, 11:37 PM
:lol:

I don't think anyone's ever going to use anywhere near 65,000 rows either. I think it's just a "because we can and it makes us look better than Microsoft" thing.

Of course, then, since Corel isn't Microsoft, they are better anyway. :D

Agent_24
27-03-2007, 11:41 PM
Also forgot to mention it goes up to Column ZZZ ....

Thomas01
28-03-2007, 05:07 PM
Wow!! All this maths stuff. It's coming back now. Glad I am retired and have forgotten lots of it. But it is absorbing and satisfying. I always fancied the duo decimal stuff (base 12) - perhaps somebody can enlighten me. Our instructor 50 years ago reckoned pi would work out. I admit I told my students that I believed that was true but I never checked it. Has anybody else?
Messing about with maths to be honest I still find to be satisfying and rewarding. But I hate these private school people who insist that they can make learning maths fun. It isn't, it's hard work and can be terribly boring.
But what a reward when it is learned. I specialised for a while calculating deflections in aircraft rigs etc. Greatly satisfying when things worked out to my calculations. But it took me hours and hours of study for years to reach that point.
Tom

TGoddard
28-03-2007, 08:00 PM
Pi isn't a rational number. It doesn't matter what base you use, you can't represent it in a finite number of digits unless the base is itself a multiple of Pi, which would make nearly everything else unrepresentable :) . Whether or not any particular activity is fun is entirely subjective - a good school may well increase the proportion who enjoy a particular subject.