Errata.... Sorry for the incessant postings.
But with the original TRUNC formula, -0.0382 and 0.0382 never (!) occur
"Never say never!"
In my zeal to avoid TMI, I forgot to address a possible retort that I anticipated.
The fact is: the original TRUNC formula
can return 0.0382 and -0.0382. But only as an accident of implementation -- a defect, IMHO.
To understand that, note that TRUNC(20*0.999999999999998) returns 20 (!), as does INT(20*0.999999999999998).
Obviously, they should not, since 20*0.999999999999998 should be
and is less than 20.
The misbehavior of TRUNC and INT is
not due to limitations of 64-bit binary floating-point arithmetic. Note that 20*0.999999999999998
-20
<0 returns TRUE, which confirms that 20*0.999999999999998 results in a binary value that is indeed less than 20.
Instead, the problem is: TRUNC and INT arbitrarily round their argument to 15 significant digits before performing their operation. And since the exact decimal representation of the binary result of 20*0.999999999999998 is 19.9999999999999,
60920149533194489777088165283203125, that rounds to 20. Of course, TRUNC(20) and INT(20) are 20.
(For similar reasons, 20*0.999999999999998
<20 returns FALSE (!). Excel arbitrarily rounds the left and right operands of comparison operators to 15 significant digits, just for the purpose of the comparison.)
-----
In theory, Excel RAND might return a value as large as 1-2^-53 (0.99999999999999989) and as small as 2^-53 (0.00000000000000011102230246251565).
(I rounded the decimal approximations to 17 significant digits.)
So yes, TRUNC(2*0.0382*
(1-x*2^-53)-0.0382,4) does return 0.0382 for x=1,2,3,4,5. But it returns 0.0381 for x>=6. In all cases, the first argument is less than 0.0382. So, it should round down to 0.0381. This is demonstrated by the table below.
Likewise, TRUNC(2*0.0382*
x*2^-53-0.0382,4) does return -0.0382 for x=1,2,3,4,5. But it returns -0.0381 for x>=6. In all cases, the first argument is greater (closer to zero) than -0.0382. So, it should round down toward zero to -0.0381.
Moreover, the probability that the original TRUNC formula returns 0.0382 and -0.0382 is 1 / 1,801,439,850,948,198.5 (about 0.000000000000000555111512312578) for each. That is infinitesimally less than the uniform probability of each 4dp value between 0.0382 and -0.0382, which is 1/765 (about 0.00130718954248366).
Nevertheless, I should have written: with the original TRUNC formula, -0.0382 and 0.0382
almost never (!) occur.
-----
PS..... Yes, that means that my corrected TRUNC and alternative INT formulas might incorrectly return 0.0383 and -0.0383, at least in theory. If we are worried about the infinitesimal possibility
, the prudent implementation of those formulas is:
=
MAX(-0.0382, MIN(0.0382, ROUND(TRUNC((2*0.0382+0.0001)*RAND(), 4) - 0.0382, 4)
))
=
MAX(-0.0382, MIN(0.0382, INT((2*382+1)*RAND() - 382) / 10000
))
But I do not know if the RAND in Excel 2010 and later actually returns 1-5*2^-53 to 1-2^-53 or 2^-53 to 5*2^-53. I stumbled across the problem with INT(20*0.999999999999998) in Excel 2003, which had a very different implementation of RAND.
The uber-simpler formula =RANDBETWEEN(-382,382)/10000 is looking more and more attractive.
-----
rand v randbtwn.xlsm |
---|
|
---|
| A | B | C | D | E | F | G |
---|
1 | | | | +residual.... | exact C | | |
---|
2 | | max RAND | 1.000000000000000000000000000000 | -1.11E-16 | 0.999999999999999,88897769753748434595763683319091796875 | | |
---|
3 | | min RAND | 0.000000000000000111022302462516 | -3.45E-31 | 0.000000000000000111022302462515,65404236316680908203125 | | |
---|
4 | | | | | | | |
---|
5 | 2^-53 times.... | TRUNC(C,4) | 2*0.0382*(1-A*2^-53)-0.0382 | +residual.... | exact C | C<0.0382 | 1-A*2^-53 |
---|
6 | 1 | 0.0382 | 0.0382000000000000 | -1.39E-17 | 0.0381999999999999,839683795244127395562827587127685546875 | TRUE | 0.999999999999999,88897769753748434595763683319091796875 |
---|
7 | 2 | 0.0382 | 0.0382000000000000 | -1.39E-17 | 0.0381999999999999,839683795244127395562827587127685546875 | TRUE | 0.999999999999999,7779553950749686919152736663818359375 |
---|
8 | 3 | 0.0382 | 0.0382000000000000 | -2.78E-17 | 0.0381999999999999,7009059171659828280098736286163330078125 | TRUE | 0.999999999999999,66693309261245303787291049957275390625 |
---|
9 | 4 | 0.0382 | 0.0382000000000000 | -2.78E-17 | 0.0381999999999999,7009059171659828280098736286163330078125 | TRUE | 0.999999999999999,555910790149937383830547332763671875 |
---|
10 | 5 | 0.0382 | 0.0382000000000000 | -4.16E-17 | 0.0381999999999999,56212803908783826045691967010498046875 | TRUE | 0.999999999999999,44488848768742172978818416595458984375 |
---|
11 | 6 | 0.0381 | 0.0381999999999999 | +4.16E-17 | 0.0381999999999999,4233501610096936929039657115936279296875 | TRUE | 0.999999999999999,3338661852249060757458209991455078125 |
---|
12 | | | | | | | |
---|
13 | 2^-53 times.... | TRUNC(C,4) | 2*0.0382*A*2^-53-0.0382 | +residual.... | exact C | C>-0.0382 | A*2^-53 |
---|
14 | 1 | -0.0382 | -0.0382000000000000 | +6.94E-18 | -0.0381999999999999,90907273428319967933930456638336181640625 | TRUE | 0.000000000000000111022302462515,65404236316680908203125 |
---|
15 | 2 | -0.0382 | -0.0382000000000000 | +1.39E-17 | -0.0381999999999999,839683795244127395562827587127685546875 | TRUE | 0.000000000000000222044604925031,3080847263336181640625 |
---|
16 | 3 | -0.0382 | -0.0382000000000000 | +2.78E-17 | -0.0381999999999999,7009059171659828280098736286163330078125 | TRUE | 0.000000000000000333066907387546,96212708950042724609375 |
---|
17 | 4 | -0.0382 | -0.0382000000000000 | +3.47E-17 | -0.0381999999999999,63151697812691054423339664936065673828125 | TRUE | 0.000000000000000444089209850062,616169452667236328125 |
---|
18 | 5 | -0.0382 | -0.0382000000000000 | +4.16E-17 | -0.0381999999999999,56212803908783826045691967010498046875 | TRUE | 0.000000000000000555111512312578,27021181583404541015625 |
---|
19 | 6 | -0.0381 | -0.0381999999999999 | -4.86E-17 | -0.0381999999999999,49273910004876597668044269084930419921875 | TRUE | 0.000000000000000666133814775093,9242541790008544921875 |
---|
20 | | | | | | | |
---|
21 | | | 0.0382000000000000 | +0.00E+00 | 0.0381999999999999,9784616733222719631157815456390380859375 | | |
---|
22 | | P(TRUNC=0.0382) | 0.000000000000000555111512312578 | +2.96E-31 | 1 / 1,801,439,850,948,198.5 | | |
---|
23 | | P(0.0382) | 0.001307189542483660000000000000 | +0.00E+00 | 1 / 765 | | |
---|
|
---|