Results 1 to 6 of 6
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Random Number (MSO 2003/2007)

    I have a list of numbers, which are all 10 digits. What I am trying to do is generate a 3 digit random number from those 10 number combinations.
    I also want it to show this way: xxx W xxx The formula I used was =RANDBETWEEN(1,B3) and I formated the cell to (000 "W" 000). The formula works, and the format works for the numbers behind the "W" but in front of the "W" I get 6 digits. I tried reducing it to a single 0 before the "W" but I still get 6 digits.
    Ideas?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random Number (MSO 2003/2007)

    Howsabout

    =LEFT(RANDBETWEEN(1,$B$3),3) & " W " & LEFT(RANDBETWEEN(1,$B$3),3)
    Jerry

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Random Number (MSO 2003/2007)

    Thanks Jerry that seemed to work. I copied and pasted it into the appropriate cell, and removed the "$" so that the formula would generate to the adjacent cell and not the first cell (see attachment) Now however when I change any of the numbers in Column "B", or add a number, column "D" re-generates a random number for all of them. In looking in the help in Excel, it says that by putting the "=" sign there it should not do that.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random Number (MSO 2003/2007)

    The RAND and RANDBETWEEN functions will be recalculated, i.e. generate a new random number, each time the worksheet is recalculated.
    You can edit the formula (press F2), then press F9 to replace the formula with its result.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random Number (MSO 2003/2007)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Good point. Then my quick and dirty way to get around it would be a cut and paste special and select value <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I tried taking away auto calculate in the options menu to no avail <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Jerry

  6. #6
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Random Number (MSO 2003/2007)

    Perfect Guys! thank you.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

Posting Permissions

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