+ Reply to Thread
Results 1 to 4 of 4

Generate list of random numbers (1 to 6) without repeating last and second last number?

  1. #1
    Registered User
    Join Date
    11-25-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    13

    Generate list of random numbers (1 to 6) without repeating last and second last number?

    I'm trying to solve this problem with several functions but didn't managed to get the final result.

    PROBLEM:
    I want to generate a 1000 random numbers (range is from 1 to 6), but without repeating last and second last number. For example:

    This is good: 3, 5, 1, 2, 3, 5, 6, 2, 1, 6, 4, 3, 5, 1, 6, ...

    This is not good: 3, 5, 1, 2, 2, ... (because number 2 is showing two times in a row)

    This is not good: 3, 5, 1, 2, 1, ... (because number 1 is showing again after number 2)

    I'm trying to generate the list of random numbers but following this rules above, so it's not fully random list of numbers ...
    Do I need to use array formulas?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Generate list of random numbers (1 to 6) without repeating last and second last number

    Try this

    Assuming A1 is blank or a text header then use this formula in A2

    =RANDBETWEEN(1,6)

    and then this formula in A3 copied down as far as you want

    =AGGREGATE(15,6,{1,2,3,4,5,6}/(COUNTIF(A1:A2,{1,2,3,4,5,6})=0),RANDBETWEEN(1,6-COUNT(A1:A2)))
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-25-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    13

    Re: Generate list of random numbers (1 to 6) without repeating last and second last number

    This works, thank you.

    Please can you tell me what does that part 15,6 in the formula =AGGREGATE(15,6,{1,2 ...
    What is the meaning of that part?

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Generate list of random numbers (1 to 6) without repeating last and second last number

    AGGREGATE function lets you specify multiple functions within it, 15 here denotes SMALL, and 6 tells it to ignore errors, so when we use this part

    {1,2,3,4,5,6}/(COUNTIF(A1:A2,{1,2,3,4,5,6})=0)

    That generates an array of the four numbers we need, e.g. if 2 and 6 are the previous two numbers that generates this array

    {1,#DIV/0!,3,4,5,#DIV/0!}

    the two errors are ignored and the randbetween at the end generates the k value between 1 and 4 to return the kth smallest of the above array.

    It would be equally OK to use 14 for LARGE

    using AGGREGATE function means "array entry" isn't required but in older versions of excel you could use this "array formula" to do the same

    =LARGE(IF(COUNTIF(A1:A2,{1,2,3,4,5,6})=0,{1,2,3,4,5,6}),RANDBETWEEN(1,6-COUNT(A1:A2)))

    confirm with CTRL+SHIFT+ENTER

+ 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. [SOLVED] Need help adapting a formula to generate a random array of non-repeating numbers
    By MalibuLX3 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-11-2016, 08:56 AM
  2. [SOLVED] How to generate non-repeating random numbers in a column wihtout VB
    By luv2glyd in forum Excel General
    Replies: 4
    Last Post: 02-16-2015, 12:43 AM
  3. How to generate non-repeating random numbers
    By luv2glyd in forum Excel General
    Replies: 2
    Last Post: 09-24-2010, 12:16 PM
  4. using excel generate random numbers 1 to 52 without repeating
    By Norman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2006, 02:25 AM
  5. Replies: 0
    Last Post: 06-05-2006, 07:15 PM
  6. [SOLVED] How do I generate random, non-repeating numbers that don't change?
    By bsquared0 in forum Excel General
    Replies: 1
    Last Post: 07-30-2005, 12:05 AM

Tags for this Thread

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