Mike wrote:
My Excel code for this one-cell routine is described a short writeup at www.MikeLampton.com, entitled Random Numbers for Spreadsheets.
Although this has the
appearance of being published in the J of Stat Software, I notice that the issue and volume information are boilerplated. Where are you in the submission/acceptance process?
There are some details that are incorrect. It would be nice to correct them before publication.
I have not completed my "review". But for example:
1. IEEE-754 64-bit floating-point can accurately represent integers in the range of -2^53 to +2^-53, not -2^52 to +2^52-1 as the article states.
And in any case, the range is completely symmetrical: -2^x to +2^x, not -2^x to +2^x-1
2. "Spreadsheets" (Excel at least) do
not "typically deliver exact integer arithmetic only for numbers whose size is less than about 10^15, i.e. fifteen digits accuracy".
That is an oft-repeated fallacy even in Microsoft online documentation.
You can validate both corrections with the following simple experiment:
1. In A1, enter the formula =2^53. (Alternatively, enter =-2^53.)
2. In A2, enter the formula =SUM(A1,-TEXT(A1,"0.00000000000000E+0")
Note that Excel does display A1 only up to 15 significant digits (formatted as Number), namely 9007199254740990. But the formula in A2 returns 2. That demonstrates that the value in A1 is indeed 9007199254740992, the exact value of 2^53.
With respect to point #2, we get into the semantics of terms like "typical" and "deliver".
For example, if the formula in A2 were =A1-TEXT(A1,"0.00000000000000E+0"), the result would indeed be zero(!). Then again, if A2 were =A1-TEXT(A1,"0.00000000000000E+0")-0, the result would be 2. (The -0 represents any subexpression whose result is zero.)
That inconsistency is due to the dubious heuristic poorly described under the misleading title "Example When a Value Reaches Zero" at http://support.microsoft.com/kb/78113. I say "dubious", in part, for the very reason that is applied inconsistently in Excel.
It is true that Excel
inputs and
displays only up to 15 significant digits. For the same reason, an expression evaluated in VBA macro (Sub), then
stored into an Excel cell is limited to 15 significant digits. For example, Range("A3")=2^53 does indeed result in exactly 9007199254740990, not 9007199254740992.
(But a VBA
Function returns the exact 64-bit floating-point value,
not limited to 15 significant digits.)
Also, if the calculation option "Precision as displayed" is set (which you should never do for scientific calculations) and a cell is formatted as General, the cell value is rounded to 15 significant digits.
But
generally, arithmetic calculations are carried out to the full precision supported by IEEE-754 64-bit floating-point. In fact, that is the most-common reason why MATCH and other look-up operations fail to
behave as expected. It is also why IF(10.1-10=0.1,TRUE) returns FALSE(!).