=RANDBETWEEN and Decimals?

Sub_Diver

New Member
Joined
May 19, 2003
Messages
17
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try:

=RANDBETWEEN(900000,1000000)/1000
 
Upvote 0
=RANDBETWEEN($A$1,$B$1)+RANDBETWEEN($A$1,$B$1)/1000

or

=--(RANDBETWEEN($A$1,$B$1)&"."&RANDBETWEEN($A$1,$B$1))
 
Upvote 0
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?
 
Upvote 0
=RANDBETWEEN(A1*1000, B1*1000) /1000

should work
 
Upvote 0
=(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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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
Back
Top