Try:
=RANDBETWEEN(900000,1000000)/1000
This is a discussion on =RANDBETWEEN and Decimals? within the Excel Questions forums, part of the Question Forums category; Need help with allowing three decimal places of random numbers generated between two numbers. so Cell A1 would be 900.000 ...
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
Try:
=RANDBETWEEN(900000,1000000)/1000
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
How about;
=(RANDBETWEEN(900,1000)+(ROUND((RAND()),3)))
-Mike D
=RANDBETWEEN($A$1,$B$1)+RANDBETWEEN($A$1,$B$1)/1000
or
=--(RANDBETWEEN($A$1,$B$1)&"."&RANDBETWEEN($A$1,$B$1))
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?
=RANDBETWEEN(A1*1000, B1*1000) /1000
should work
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
Did you try what I suggested?
=(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!
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
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
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
Like this thread? Share it with others