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
535349	2007-03-25 04:03:00	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	Thomas01 (317)
535350	2007-03-25 04:41:00	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.	pctek (84)
535351	2007-03-25 06:33:00	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	Thomas01 (317)
535352	2007-03-25 22:55:00	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 .	dolby digital (5073)
535353	2007-03-26 08:19:00	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.	pico (4752)
535354	2007-03-26 09:13:00	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.	TideMan (4279)
535355	2007-03-26 09:24:00	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.	somebody (208)
535356	2007-03-26 11:16:00	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 .	porkster (6331)
535357	2007-03-26 22:19:00	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	Thomas01 (317)
535358	2007-03-27 05:24:00	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.	Graham L (2)
1 2 3