RSS
Twitter
Facebook
YouTubeI was using Microsoft Excel to do a column of numbers and something very strange happened.
824.7 - 789.7 - 34.7 = -0.000000000000007105427357601
Like, huh? What? It should be 0, not a negative number that is so small that it is essentially 0.
Columbus Underground Messageboard » General Columbus Discussion » Everyday Chit Chat
excel math problem
[14 posts] [8 contributors]
Topic Rating:
(Total Ratings: )
Rate this topic:
Rate this topic:
-
Rockmastermike
CORNspiracy HQ
Login to Send PM
Posted 4 months ago # -
hugh59
Aboard the ultimate power in Olde Towne East
Login to Send PM
hugh59 said:
I was using Microsoft Excel to do a column of numbers and something very strange happened.824.7 - 789.7 - 34.7 = -0.000000000000007105427357601
Like, huh? What? It should be 0, not a negative number that is so small that it is essentially 0.
Oops, typo. The equation was:
824.4 - 789.7 - 34.7 = -0.000000000000007105427357601
Sorry about that. I just tried it again on a freshly opened window of excel and got the same error.
Posted 4 months ago # -
th0m
Beechwold
Login to Send PM
i can't believe i'm actually going to defend microsoft, but no it isn't them, and you'll find this problem with all computers. sorry the link was rather deep. the basics are a precision number with some significant digits like 3.1415626 plus a multiplier of ten is much more useful and easier for a computer to represent decimals than trying to represent 3.0000000000000000000000001 precisely. most software should correct for most things, and in excel you should always format your numbers (with appropriate rounding) and also know that the general problem with floating point math will always make you be suspect of things, but there isn't much to be suspect about. If you need 1e25 precision you might be doing something like comparing an atom in a grain of salt to the current position of jupiter, each relative to the center of the galaxy, in which case you're probably well aware of precision problems in computers.
Posted 4 months ago # -
Cole
Hilltop
Login to Send PM
hugh59 said:
I should just go back to Yale University's Astronomy 160 class where 3 = pi = square root of 10.th0m said:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problemsWhich one explains why the Death Star was designed with a fatal flaw?
Posted 4 months ago # -
myliftkk
Columbus, OH
Login to Send PM
th0m said:
i can't believe i'm actually going to defend microsoft, but no it isn't them, and you'll find this problem with all computers. sorry the link was rather deep. the basics are a precision number with some significant digits like 3.1415626 plus a multiplier of ten is much more useful and easier for a computer to represent decimals than trying to represent 3.0000000000000000000000001 precisely. most software should correct for most things, and in excel you should always format your numbers (with appropriate rounding) and also know that the general problem with floating point math will always make you be suspect of things, but there isn't much to be suspect about. If you need 1e25 precision you might be doing something like comparing an atom in a grain of salt to the current position of jupiter, each relative to the center of the galaxy, in which case you're probably well aware of precision problems in computers.True, computers have the issue, as a "feature" of binary, or base 2, math. Excel however, is not a computer, it is a software program written by people who should know better than to expose the problems of the underlying machine's binary math to users (easy enough to avoid). Don't get me wrong, I use plenty of MS products, but their irritating software design knows few bounds.
Posted 4 months ago # -
hugh59
Aboard the ultimate power in Olde Towne East
Login to Send PM
I often format my numbers as "numbers" with no decimal points so that file numbers present correctly. In this case, I was using the default format for the spaces. It was just so freaking strange. Reminds me of the days when Intel moved up from the 486 chips to the Pentiums. They soon learned that the Pentiums had certain problems with math. A joke that came out at the time was, "Why did Intel call it the Pentium and not the 586? Because when they added 100 to 486 the chip answered 585.9989978977908."
Posted 4 months ago # -
chrisgillespie
Short North/Victorian Village
Login to Send PM
Oh, for heaven's sake. This is not an excel "problem", it's a case of not casting the number correctly for what you are needing to do. In fact, this is not excel specific, and although this same thing happens in SQL Server, it's also not Microsoft specific. This also happens in other databases and query languages. Here's info about numeric data conversions for IBM DB2:
I know it looks weird, and if it's a comfort, you aren't alone in being surprised by the result. I run into this several times a year with people who are able to write their own queries against databases (programmers, SA's, finance folks, etc) -- many of these folks have math or computer science degrees. So, yeah, it takes some time to get your head around this.
Mostly, the take away is, be careful to use the correct precision for what you are doing, and cast (format) accordingly. It takes some practice to get used to it
...or blame the software, and never really know if your results are accurate ;)
Posted 4 months ago # -
th0m
Beechwold
Login to Send PM
hugh59 said:
I often format my numbers as "numbers" with no decimal points so that file numbers present correctly. In this case, I was using the default format for the spaces. It was just so freaking strange. Reminds me of the days when Intel moved up from the 486 chips to the Pentiums. They soon learned that the Pentiums had certain problems with math. A joke that came out at the time was, "Why did Intel call it the Pentium and not the 586? Because when they added 100 to 486 the chip answered 585.9989978977908."yeah that was a funny time! that's about the time that I learned about floating point precision problems, i was just getting into databases then. :P To understand the details of what was going on with that, you had to realize the subtlety that floating point stuff has the lack of precision anyway, but this thing was just getting it wrong even with that in mind. i honestly wondered if you had remembered that, and feared that this was more of that but anyway, i appreciate the forum to discuss it, it is an important topic!
Posted 4 months ago #
You must log in to post.
Launched in August 2010, TheMetropreneur.com is a local online resource devoted to small business development and entrepreneurship. Its aim is to tell the stories of Central Ohio's business community, foster regional economic development and assist entrepreneurs with its resource-heavy focus.