Page 1 of 2 12 LastLast
Results 1 to 10 of 13

=RANDBETWEEN and Decimals?

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 ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    17

    Default =RANDBETWEEN and Decimals?

    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

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,899

    Default Re: =RANDBETWEEN and Decimals?

    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!"

  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    21

    Default Re: =RANDBETWEEN and Decimals?

    How about;

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

    -Mike D

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,981

    Default Re: =RANDBETWEEN and Decimals?

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

    or

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

  5. #5
    New Member
    Join Date
    May 2003
    Posts
    17

    Default Re: =RANDBETWEEN and Decimals?

    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?

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,899

    Default Re: =RANDBETWEEN and Decimals?

    =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!"

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,981

    Default Re: =RANDBETWEEN and Decimals?

    Did you try what I suggested?

  8. #8
    New Member
    Join Date
    May 2003
    Posts
    17

    Default Re: =RANDBETWEEN and Decimals?

    =(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!

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,899

    Default Re: =RANDBETWEEN and Decimals?

    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!"

  10. #10
    New Member
    Join Date
    Jul 2002
    Posts
    21

    Default Re: =RANDBETWEEN and Decimals?

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com