Random number between negative and positive number with 4 decimal places

colink2

New Member
Joined
Apr 15, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,251
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=TRUNC((0.0382+0.0382)*RAND()-0.0382,4)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows
=RANDBETWEEN(-382,382)/10000
 
Solution

colink2

New Member
Joined
Apr 15, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
Thanks to @Fluff and joeu2004, both return the correct results. I will go for the easier one!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,251
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows
=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.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFG
1+residual....exact C
2max RAND1.000000000000000000000000000000-1.11E-160.999999999999999,88897769753748434595763683319091796875
3min RAND0.000000000000000111022302462516-3.45E-310.000000000000000111022302462515,65404236316680908203125
4
52^-53 times....TRUNC(C,4)2*0.0382*(1-A*2^-53)-0.0382+residual....exact CC<0.03821-A*2^-53
610.03820.0382000000000000-1.39E-170.0381999999999999,839683795244127395562827587127685546875TRUE0.999999999999999,88897769753748434595763683319091796875
720.03820.0382000000000000-1.39E-170.0381999999999999,839683795244127395562827587127685546875TRUE0.999999999999999,7779553950749686919152736663818359375
830.03820.0382000000000000-2.78E-170.0381999999999999,7009059171659828280098736286163330078125TRUE0.999999999999999,66693309261245303787291049957275390625
940.03820.0382000000000000-2.78E-170.0381999999999999,7009059171659828280098736286163330078125TRUE0.999999999999999,555910790149937383830547332763671875
1050.03820.0382000000000000-4.16E-170.0381999999999999,56212803908783826045691967010498046875TRUE0.999999999999999,44488848768742172978818416595458984375
1160.03810.0381999999999999+4.16E-170.0381999999999999,4233501610096936929039657115936279296875TRUE0.999999999999999,3338661852249060757458209991455078125
12
132^-53 times....TRUNC(C,4)2*0.0382*A*2^-53-0.0382+residual....exact CC>-0.0382A*2^-53
141-0.0382-0.0382000000000000+6.94E-18-0.0381999999999999,90907273428319967933930456638336181640625TRUE0.000000000000000111022302462515,65404236316680908203125
152-0.0382-0.0382000000000000+1.39E-17-0.0381999999999999,839683795244127395562827587127685546875TRUE0.000000000000000222044604925031,3080847263336181640625
163-0.0382-0.0382000000000000+2.78E-17-0.0381999999999999,7009059171659828280098736286163330078125TRUE0.000000000000000333066907387546,96212708950042724609375
174-0.0382-0.0382000000000000+3.47E-17-0.0381999999999999,63151697812691054423339664936065673828125TRUE0.000000000000000444089209850062,616169452667236328125
185-0.0382-0.0382000000000000+4.16E-17-0.0381999999999999,56212803908783826045691967010498046875TRUE0.000000000000000555111512312578,27021181583404541015625
196-0.0381-0.0381999999999999-4.86E-17-0.0381999999999999,49273910004876597668044269084930419921875TRUE0.000000000000000666133814775093,9242541790008544921875
20
210.0382000000000000+0.00E+000.0381999999999999,9784616733222719631157815456390380859375
22P(TRUNC=0.0382)0.000000000000000555111512312578+2.96E-311 / 1,801,439,850,948,198.5
23P(0.0382)0.001307189542483660000000000000+0.00E+001 / 765
Sheet1
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows
=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,982
Messages
5,621,964
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top