Or did my elementary school teachers lie to me?
April 2, 2009 11:49 AM Subscribe
Which one is right, Google or Excel?
I've discovered that Google's calculator and Excel give slightly different results when converting metric and English weights. For example, when I convert 1 gram to ounces in Excel, I get: 0.035273972 whereas Google gives me 0.035273962.
I just tried converting pounds to ounces in Excel. It's telling me that 1 pound contains 16.000002342941 ounces. Is that an error of some kind in Excel, or am I missing something?
I've discovered that Google's calculator and Excel give slightly different results when converting metric and English weights. For example, when I convert 1 gram to ounces in Excel, I get: 0.035273972 whereas Google gives me 0.035273962.
I just tried converting pounds to ounces in Excel. It's telling me that 1 pound contains 16.000002342941 ounces. Is that an error of some kind in Excel, or am I missing something?
Best answer: Floating-point precision is well-known problem in Excel: Microsoft details the error in this knowledge base article.
The official definition of an (US avoirdupois) ounce can be found here. Using the exact value of 1 oz = 28.349523125 g, NIST gives the inverse as 0.03527396 g/oz (av.), so I'd say Google is (more) correct.
posted by bonehead at 12:02 PM on April 2, 2009
The official definition of an (US avoirdupois) ounce can be found here. Using the exact value of 1 oz = 28.349523125 g, NIST gives the inverse as 0.03527396 g/oz (av.), so I'd say Google is (more) correct.
posted by bonehead at 12:02 PM on April 2, 2009
Simply put, floating point calculations on computers are only arbitrarily precise. Some numbers, like 0.1, can't be represented in binary without loss of precision. So numbers get rounded off. Surprisingly, there are a number of ways to round off; the way that's used often can't be (easily) selected by a user or programmer.
posted by orthogonality at 12:02 PM on April 2, 2009
posted by orthogonality at 12:02 PM on April 2, 2009
That's got to be an error in excel - there are 16 ounces to a pound by definition.
posted by pombe at 12:03 PM on April 2, 2009
posted by pombe at 12:03 PM on April 2, 2009
This seems to suggest Google is right, and this could give the possible explanation that Google and Excel are using different grams, "Its size can vary from system to system depending on the country." I can't speak to the credibility of the second site, but nist.gov looks legit.
posted by SAC at 12:06 PM on April 2, 2009
posted by SAC at 12:06 PM on April 2, 2009
It's a poorly-controlled floating-point truncation error.
posted by fantabulous timewaster at 12:07 PM on April 2, 2009
posted by fantabulous timewaster at 12:07 PM on April 2, 2009
Ah, of course it's a floating point error. I feel kind of silly that I overlooked that.
posted by pombe at 12:08 PM on April 2, 2009
posted by pombe at 12:08 PM on April 2, 2009
Using Wikipedia's conversion factor, I get the same thing as Google using the Windows Calculator, which uses arbitrary-precision math.
Excel is notorious for sloppy floating-point math.
posted by equalpants at 12:08 PM on April 2, 2009
Excel is notorious for sloppy floating-point math.
posted by equalpants at 12:08 PM on April 2, 2009
NIST's list (liked to above) isn't just another compilation of conversion tables, it's the American government body that defines what an ounce is in the US. It's officially correct.
posted by bonehead at 12:11 PM on April 2, 2009
posted by bonehead at 12:11 PM on April 2, 2009
Well, they're both wrong. What I think is happening is as follows:
Just as there is no decimal expansion which is exactly equal to 1/3 (0.3333333333333333 is never quite right, no matter how many 3's you use). So, there is no binary expansion which is precisely equal to the number of grams in an ounce.
Naturally, there is a more complex explanation on wikipedia. But it is enough to say that unless great care is taken, there are usually rounding errors on decimal caluclations with computers. And you will really miss that 0.00000001 of a gram.
I expect google and excel give different answers because they use different implementations of floating-point numbers.
posted by mjg123 at 12:11 PM on April 2, 2009
Just as there is no decimal expansion which is exactly equal to 1/3 (0.3333333333333333 is never quite right, no matter how many 3's you use). So, there is no binary expansion which is precisely equal to the number of grams in an ounce.
Naturally, there is a more complex explanation on wikipedia. But it is enough to say that unless great care is taken, there are usually rounding errors on decimal caluclations with computers. And you will really miss that 0.00000001 of a gram.
I expect google and excel give different answers because they use different implementations of floating-point numbers.
posted by mjg123 at 12:11 PM on April 2, 2009
Also, for those saying that it's just a floating-point error, the problem is more subtle than that. It's not just a normal double-precision floating-point rounding error (which would be predictable under IEEE 754), it's a particularly MS Excel-specific rounding error, and thus rather unpredicatable.
posted by bonehead at 12:15 PM on April 2, 2009
posted by bonehead at 12:15 PM on April 2, 2009
Just as there is no decimal expansion which is exactly equal to 1/3 [...]
Come on, man, that's overkill. Sure, Google's answer is "wrong" in that it's not exact, it's rounded to 9 digits. But it's correctly rounded. It is the closest 9-decimal-digit number to the true value. It is more correct than Excel, in a very important sense.
posted by equalpants at 12:20 PM on April 2, 2009
Come on, man, that's overkill. Sure, Google's answer is "wrong" in that it's not exact, it's rounded to 9 digits. But it's correctly rounded. It is the closest 9-decimal-digit number to the true value. It is more correct than Excel, in a very important sense.
posted by equalpants at 12:20 PM on April 2, 2009
Response by poster: Ah, I had heard that Excel had problems with floating-point operations before, but I didn't realize what that meant.
Just to make sure I understand: it sounds like the problem that everyone faces is a loss of precision when you switch from fractions to binary (sort of like the problem of switching from fractions to decimal numbers, like mjg123 noted above).
And it sounds like the problem specifically with Excel is that they do their rounding differently from everyone else in some way.
It just seems a bit absurd to me that they didn't get 1lb = 16oz right. I wonder how their conversion formula works.
posted by JDHarper at 12:34 PM on April 2, 2009
Just to make sure I understand: it sounds like the problem that everyone faces is a loss of precision when you switch from fractions to binary (sort of like the problem of switching from fractions to decimal numbers, like mjg123 noted above).
And it sounds like the problem specifically with Excel is that they do their rounding differently from everyone else in some way.
It just seems a bit absurd to me that they didn't get 1lb = 16oz right. I wonder how their conversion formula works.
posted by JDHarper at 12:34 PM on April 2, 2009
My own 2 cents: in a real world scenario how is the inaccuracy of either Google or Excel relevant in regards to weighing something? The user and instrumentation error are going to far out-weigh the floating point representation and arithmetic error. If you ever weighed something, and then reported that to me as being 16.000002342941, I would probably LMAO because there is no way you have that level of accuracy (never mind precision).
So my answer is, Google and Excel are "equally good enough." (Awkward statement lol).
posted by nickerbocker at 12:36 PM on April 2, 2009
So my answer is, Google and Excel are "equally good enough." (Awkward statement lol).
posted by nickerbocker at 12:36 PM on April 2, 2009
Response by poster: Yeah, for what I'm doing now, I don't need precision to twenty decimal places, but in some cases precision really matters.
posted by JDHarper at 12:43 PM on April 2, 2009
posted by JDHarper at 12:43 PM on April 2, 2009
Failing to convert from metric is not a precision error. They are completely unrelated.
posted by 0xFCAF at 12:48 PM on April 2, 2009
posted by 0xFCAF at 12:48 PM on April 2, 2009
the error occurred because lockheed martin and nasa used two different units of measurement. the wikipedia article article covers it in detail.
posted by phil at 12:51 PM on April 2, 2009
posted by phil at 12:51 PM on April 2, 2009
it sounds like the problem that everyone faces is a loss of precision when you switch from fractions to binary
That's half of the problem (and the reverse too-when changing from binary back into decimal). The other half of it are the rounding/truncation errors that happen during the division step(s).
in a real world scenario how is the inaccuracy of either Google or Excel relevant in regards to weighing something?
Problems happen if you want to use that number to start calculating other things and have to do multiple steps. Precision errors can propagate quickly if you're not careful. If, for example, you're doing an analysis that requires minimization, like a non-linear regression, errors can pile up quite quickly. Starting with 9 or 10 decimals of precision, it's not uncommon to end up with only 5 or 6 by the time the minimization process is complete, hundreds or thousands of operations later.
posted by bonehead at 12:53 PM on April 2, 2009
That's half of the problem (and the reverse too-when changing from binary back into decimal). The other half of it are the rounding/truncation errors that happen during the division step(s).
in a real world scenario how is the inaccuracy of either Google or Excel relevant in regards to weighing something?
Problems happen if you want to use that number to start calculating other things and have to do multiple steps. Precision errors can propagate quickly if you're not careful. If, for example, you're doing an analysis that requires minimization, like a non-linear regression, errors can pile up quite quickly. Starting with 9 or 10 decimals of precision, it's not uncommon to end up with only 5 or 6 by the time the minimization process is complete, hundreds or thousands of operations later.
posted by bonehead at 12:53 PM on April 2, 2009
Response by poster: Oops! Quite right. I misremembered the story and posted it without reading it again. (I thought they had made an error in conversion, not that they hadn't converted at all.)
posted by JDHarper at 12:55 PM on April 2, 2009
posted by JDHarper at 12:55 PM on April 2, 2009
This seems to suggest Google is right, and this could give the possible explanation that Google and Excel are using different grams, "Its size can vary from system to system depending on the country."
SAC, a gram's a gram the world round.
A gram is an SI unit of measure. Previously, some of the predecessors of SI units (in the original metric system) were defined in slightly different ways, but AFAIK the gram has always been defined as 1/1000 of the mass of the definitive 1 kg platinum mass (kept in a vacuum somewhere).
Countries that use the SI system have only one gram definition, therefore.
And, nickerbocker, for whom Google and Excel are "equally good enough.", please don't apply for work at either of my most recent employers. The attitude may be appropriate for shop work, but scientists require mathematical accuracy.
posted by IAmBroom at 9:09 PM on April 2, 2009
SAC, a gram's a gram the world round.
A gram is an SI unit of measure. Previously, some of the predecessors of SI units (in the original metric system) were defined in slightly different ways, but AFAIK the gram has always been defined as 1/1000 of the mass of the definitive 1 kg platinum mass (kept in a vacuum somewhere).
Countries that use the SI system have only one gram definition, therefore.
And, nickerbocker, for whom Google and Excel are "equally good enough.", please don't apply for work at either of my most recent employers. The attitude may be appropriate for shop work, but scientists require mathematical accuracy.
posted by IAmBroom at 9:09 PM on April 2, 2009
Some numbers, like 0.1, can't be represented in binary without loss of precision
This is not true.
posted by rodgerd at 10:57 PM on April 2, 2009
This is not true.
posted by rodgerd at 10:57 PM on April 2, 2009
This thread is closed to new comments.
posted by Smarson at 11:52 AM on April 2, 2009