+ Reply to Thread
Results 1 to 9 of 9

Prevent random numbers from changing?

  1. #1
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    155

    Prevent random numbers from changing?

    Start from scratch.xlsx


    I would like to simply rename a copy of this sheet each week to week 15, week 16 etc.

    At the moment I have it to generate random numbers for a reference (Its only a very simple check if there are any queries and doesn't need to be anymore than a random number)

    When I copy and create a new week I would like it to create new reference numbers once only then keep those numbers and NOT update them each time the sheet is opened...... is this possible?

    I would like to keep it very simple so anyone can create a new week without having to let them have passwords, change formula etc.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Prevent random numbers from changing?

    In T5 and filled down

    =IF($T$2="",RANDBETWEEN(10000000,99999999),T5)

    The random numbers will be locked when anything is entered into T2 and refreshed when it is blank.

    Might need to enable iterative calculation on excel options.

    Does that help?

  3. #3
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    155

    Re: Prevent random numbers from changing?

    Is it possible to lock the reference numbers in rows in column T if anything is entered into the same row in column B? Or even lock all the reference numbers in the whole of column T if any information is entered into any cell in column B??

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Prevent random numbers from changing?

    Try changing the condition of the logical test.

    To lock individual rows as column B is filled.

    =IF($B5="",RANDBETWEEN(10000000,99999999),T5)

    To lock the whole column when one row in column B is filled.

    =IF(COUNTA($B$5:$B$86)=0,RANDBETWEEN(10000000,99999999),T5)

  5. #5
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    155

    Re: Prevent random numbers from changing?

    Thank you Jason. Thats one more problem solved :-)

  6. #6
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    155

    Re: Prevent random numbers from changing?

    It stopped the numbers changing as soon as I type anything into T2 and worked fine until I save and re-open the sheet and I get a warning about a circular reference?

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Prevent random numbers from changing?

    Easy to fix

    File > Options > Formulas

    Tick the box to 'enable iterative calculations'

  8. #8
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    155

    Re: Prevent random numbers from changing?

    Fixed! Thank you Jason

  9. #9
    Registered User
    Join Date
    09-16-2016
    Location
    Reykjavik, Iceland
    MS-Off Ver
    Excel for Mac 16.61
    Posts
    2

    Re: Prevent random numbers from changing?

    I found I needed persisted random numbers and/or reproducible random number sequences all the time. Turns out this is possible using only standard spreadsheet formulas.

    One way is to use the so called Lehmer random number method. It generates a sequence of random numbers in your spreadsheet that stays the same until you change the "seed number", a number you choose yourself and will recreate a different random sequence for each seed number you choose.

    The short version:
    1. In cell B1, enter your "seed" number, it can be any number from 1 to 2,147,483,647
    2. In cell B2 enter the formula =MOD(48271*B1,2^31-1) , this will generate the first random number of your sequence.
    3. Now copy this cell down as far as the the random sequence you want to generate.

    That's it! If you want a different set of numbers, just change the seed number in B1. If you ever want to recreate the same set of numbers again, just use the same seed and the same random sequence will appear.

    Note: The random numbers will be in the range 1 to 2,147,483,647. To normalize them to the range 0 to 1 as the results of the built-in RAND function, go to cell C2 and enter the formula =B2/(2^31-1). Then fill that formula down as far as your random number sequence in column B.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Filtering random numbers without regenerating random numbers
    By ptack in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-07-2022, 04:03 PM
  2. Stopping dates changing to random numbers
    By thingstodo123 in forum Excel General
    Replies: 5
    Last Post: 12-09-2015, 05:57 AM
  3. [SOLVED] Prevent Excel from rudely changing text to numbers
    By Oppressed1 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-02-2014, 04:41 PM
  4. Prevent Excel from Changing Strings to Numbers
    By Ambulare in forum Excel General
    Replies: 4
    Last Post: 11-03-2008, 12:47 PM
  5. Prevent excel changing numbers data to dates.
    By Cindax in forum Excel General
    Replies: 2
    Last Post: 02-28-2006, 05:35 AM
  6. [SOLVED] Non-random numbers generated by excel's data analysis random gener
    By Allie in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-17-2005, 02:05 AM
  7. [SOLVED] Re: Non-random numbers generated by excel's data analysis random gener
    By Harlan Grove in forum Excel General
    Replies: 2
    Last Post: 09-13-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1