[Please excuse multiple postings, but I need to get feedback from several email communities.]
The discussion of Excel's limitations on edstat-l (archives are available at http://jse.stat.ncsu.edu/) has been interesting and informative. I agree substantially with David Heiser that Excel can be used for statistical analysis, but the user must exercise judgment and be knowledgeable about the software. I can also see Cryer-McCullough-et al's point that the level of knowledge required is way too high and many unsophisticated users, relying on defaults, will get miserable results. No question the product can be better.
The discussion then turned to variable declaration and David Firth posted a nice review of data types. His email signature says
David Firth
Still Thinking Different: Apple Powerbook 3400 & Newton 2100
As a former Excel Mac user (now on the Dark Side), I recently had the opportunity to debug a Mac user's add-in (Physics). Here's what I found.
I believe there is a problem with the declaration of variables in Mac Excel 2001. The largest number that the machine should be able to represent with 32-bit floating point double-precision (that's Double declaration in the code below), is supposed to be 1.79769313486232E308. In fact, in Mac Excel 2001, 1.797 * 10^38 works but 1.797* 10^39 does not! (Of course, forget about 10 ^ 308 and this was the problem with add-in.)
So Mr. Firth, and other Mac Excel users, please run the macro below on Excel 2001 on a Mac to see if you get the same behavior and let me know what happens. You'll have to add a module, copy the code from below, and then run it. If you're like me, you'll get an overflow error on the line that reads, myMaxBug = 1.797 * 10 ^ 39. Mac Excel 2001 cannot represent that number.
Sub Excel2001BugTest()
Dim myMaxOK As Double
myMaxOK = 1.797 * 10 ^ 38
Dim myMaxBug As Double
myMaxBug = 1.797 * 10 ^ 39
End Sub
A variable declared as a Single has a highest value of 3.402823E38. In the code above, Mac Excel 2001 does myMaxOK = 9* 10 ^ 38, just fine. It's not that it doesn't support a Double, it appears that, somehow, the Double has been coded for 38 instead of 308!!! How can that happen?
Note, Excel98 and all Win versions that I have tested work just fine. Only Mac Excel 2001 gives the problem. I was running it on a G4 with OS 9.1.
I have an RNG that uses the Currency data type (to use a essentially a Double Long for large integer computations) and it works just fine on Mac Excel 2001. Only Double (and Variant) don't work.
Please let me know what you find or if you have any explanations for this odd behavior.
Thanks!
Humberto Barreto
x6315
