=RANDBETWEEN and Decimals?

[Sub_Diver]

Need help with allowing three decimal places of random numbers generated between two numbers.

so Cell A1 would be 900.000 and Cell B1 would be 1000.000

I'm using:

=RANDBETWEEN(A1, B1)

I need it to give numbers with the decimal also random generated.

>>>What I'm getting 934.000, 978.000, 954.000, 976.000 - etc...

>>>What I'd like to get is 934.876, 978.993, 954.321, 976.107

Need a random 3 decimal places as well...

any ideas?

Thnx

[Joe4]

Try:

=RANDBETWEEN(900000,1000000)/1000

[mdipalma]

How about;

=(RANDBETWEEN(900,1000)+(ROUND((RAND()),3)))

-Mike D

[Aladin Akyurek]

=RANDBETWEEN($A$1,$B$1)+RANDBETWEEN($A$1,$B$1)/1000

or

=--(RANDBETWEEN($A$1,$B$1)&"."&RANDBETWEEN($A$1,$B$1))

[Sub_Diver]

In reference to first reply...

That definitely gave me the three random decimal places, but I lose the first three digits.

I.E. = 978.000

Becomes .978

What I need to do is generate a three digit/three decimal place random number from two cells who's data might be:

A1=978.023 and B1=1096.013

I need number between these two numbers whos decimal places are also random...

The "range" is a predetermined three decimal place set of two numbers.

Thnx though.. Might be able to add to your idea to come up with what I need.


Any other ideas?

[Joe4]

=RANDBETWEEN(A1*1000, B1*1000) /1000

should work

[Aladin Akyurek]

Did you try what I suggested?

[Sub_Diver]

=(RANDBETWEEN(900,1000)+(ROUND((RAND()),3)))

Definitely worked...

Thnx Much!

You wouldn't mind breaking that down (So I might understand) to a level for the ignorant would you..?

I'd like to understand whats going on in formula for future ref.. =)

Thnx again Mike D and everyone!

[Joe4]

Sub_Diver

Be careful! the
=(RANDBETWEEN(900,1000)+(ROUND((RAND()),3)))
might not always work!

It is first selecting a random number between 900 and 1000. Then it adds a random three digit decimal to it.

If it selects 1000 in the first part, your result will be over 1000. To see this, try this a few times, seeing that you will get a number greater than one:
=(RANDBETWEEN(0,1)+(ROUND((RAND()),3)))

The RANDBETWEEN function returns an Integer with no decimals. If you multiply each number by 1000 to get the random number, then divide by 1000 when you are done, you will get a three digit random number that is always between you range:

A1:900
B1:1000

=RANDBETWEEN(A1*1000,B1*1000)/1000

[mdipalma]

Sure, happy to.

=(RANDBETWEEN(900,1000)+(ROUND((RAND()),3)))

You should probably change it to;

=(RANDBETWEEN(A1,B1)+(ROUND((RAND()),3)))

if you want it to make the number be between the numbers in A1 and B1.

RANDBETWEEN(A1,B1) - Will generate a random integer between the two numbers in cells A1 and B1. This is what you had to start with, so to add the decimal

RAND() - Will generate a random number between 0 and 1. Wrapping it in the ROUND function like this;

(ROUND((RAND()),3)) - Will take the random number between 0 and 1 and round it to 3 decimal places. I then add the to together to get a number between A1 and B1 with three decimals.

You may also want to make the number format on that cell force the display of 3 decimal places. If the decimal portion is "##0" it will only display the first two places.

Hope this helps.

-Mike D