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

1. ## 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?  Register To Reply

2. ## 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)))  Register To Reply

3. ## 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?  Register To Reply

4. ## 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  Register To Reply

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