Random number between negative and positive number with 4 decimal places

[colink2]

I am trying to generate a random number with 4 decimal places between -0.0382 and 0.0382

This works fine for integers
=RANDBETWEEN(-10, 10)

I have looked at many threads but any formulas I have found return numbers outside the bounds of -0.0382 and 0.0382

Any help appreciated.

ColinK2

 

[Fluff]

How about

=TRUNC((0.0382+0.0382)*RAND()-0.0382,4)

 

[joeu2004]

=RANDBETWEEN(-382,382)/10000

 

[colink2]

Thanks to @Fluff and joeu2004, both return the correct results. I will go for the easier one!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[joeu2004]

=TRUNC((0.0382+0.0382)*RAND()-0.0382,4)

=RANDBETWEEN(-382,382)/10000

both return the correct results. I will go for the easier one!

Actually not!

I, too, prefer the RANDBETWEEN method because it is simpler and "cleaner". KISS!

But I had not considered the possibility that it is also more correct.

We expect each value from -0.0382 to 0.0382 with the probability of 1/765 (0.00130718954248366). Thus, in a sample of 10,000 values, we expect each value to occur 13 times on average.

(In one sample of 10,000 values, each value occurred from 4 to 26 times, for an average of 13.0718954248366 (!) and a sample standard deviation of 3.62420703578926.)

The RANDBETWEEN formula does exactly that.

But with the original TRUNC formula, -0.0382 and 0.0382 never (!) occur, even in 400 samples of 10,000 values each.

The TRUNC formula should be:

=ROUND(TRUNC((2*0.0382+0.0001)*RAND(), 4) - 0.0382, 4)

The +0.0001 is necessary so that the result might be 0.0382. The -0.0382 is moved outside the TRUNC expression so that the result might be -0.0382.

Arguably, the explicit ROUND is optional -- albeit prudent, IMHO. It eliminates any anomalies of the binary arithmetic, ensuring the correct binary approximation of the decimal fraction.

Essentially, it is wrong to truncate negative results toward zero.

(I was surprised, too, until I visualized how the truncated random value should map onto the real-number line, always truncating to the left.)

And since it is wrong to truncate negative results toward zero, we might as well use INT, to wit:

=INT((2*382+1)*RAND() - 382) / 10000

But that is effectively what we are doing with the RANDBETWEEN formula.

-----

I would be happy to explain more. But it might be TMI for most people.

For a "convincing" demonstration, generate random samples of 10,000 values with each formula. Use the same value of RAND() in each formula, for comparison.

You will see that the original TRUNC formula never produces 0.0382 and -0.0382. The RANDBETWEEN formula and the modified TRUNC and INT formulas do.

 

[joeu2004]

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
Sheet1

 

[joeu2004]

=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))

Oops! With the modified TRUNC and INT formulas, we don't have to worry about -0.0383. So we only need =MIN(0.0382,...). Klunk!