st: binary-decimal precision

[Chris Ruebeck <ruebeckc@lafayette.edu>]

On Feb 2, 2005, at 8:51 AM, Nick Cox wrote:
> This is the old binary-decimal precision issue again. Most multiples of 0.1 cannot be held exactly as binary numbers.

After having the math involved in this issue explained in the past, I have continued to wonder why Microsoft Excel doesn't exhibit this "feature" of binary arithmetic being represented in decimal form.  What do we give up in Excel in order to avoid it?  Or, more correctly, why do we observe it less frequently in Excel than in Stata?

The most succinct answer I could find from a very quick search of the statalist archives ("binary-decimal precision") is quoted at the end of this message: in Excel, we give up storage space in return for avoiding the puzzle.  More material is found in the following links---and, of course, in [U] 16.10 "Precision and problems therein."

http://www.stata.com/statalist/archive/2003-09/msg00234.html
http://www.stata.com/statalist/archive/2002-12/msg00490.html
http://www.stata.com/statalist/archive/2005-01/msg00318.html
http://www.stata.com/support/faqs/data/mod.html

But I would like to understand the issue better.  So, taking the example in the last link to heart, I fired up Excel and entered =mod(0.3,0.1) to find that Excel does not answer 0 either, but instead -2.77556E-17.  Yet this is much closer to zero than the 0.1 result that Stata provides when told to -display mod(0.3,0.1)-.  Note that -di %21.18f mod(.3,.1)- produces 0.099999999999999978, which is still quite far from zero (and leads to a standard output rounded to 0.1).  Note, too, that it seems a ROUND function might more consistently solve the issue in Excel than in Stata.  I leave it to others to draw potential connections between Excel's treatment and the string solutions in Stata.

It's certainly possible that there is a mistake in my analysis, I don't mean that Excel should be our benchmark for statistics, and I apologize for continuing to beat this old horse.

Chris

From � "Michael Blasnik" <michael.blasnik@verizon.net>
To � <statalist@hsphsun2.harvard.edu>
Subject � st: Re: Why does Stata do it wrong?
Date � Mon, 9 Jun 2003 09:50:26 -0400

This is a common listserv item -- the problem is one of precision.  Yes, Stata does store numbers in a different format -- different from decimal --  it's called binary and it's what computers use ;).

If you want to hold 9+ digit numbers accurately they must generated as
doubles (or sometimes longs are long enough).  try:  gen double id = .....

Given how often this question comes up on the list, I feel that perhaps StataCorp should consider shipping Stata with double as the default variable type instead of float.

Michael Blasnik
michael.blasnik@verizon.net