Create random number between 1 and 5 without getting the same number twice in a row

[HJay]

Hi,

I am trying to create a worksheet that has five different pictures appearing numerous times but I don't want the same picture to appear twice in a row. So far I have linked a picture to a number and have been using randbetween(1,5). I am presuming an if statement is needed but I have been unsuccessful in searching for a solution. When I say twice in a row I don't mean as in rows and columns I mean as in one after another. Does that make sense?

Many thanks

[TMS]

If it's random, it's random. Therefore, especially in a small sample, you may get the same number more than once. You need to store the number and compare the next number to the stored number. If it's the same, try again. When it's different, accept it and replace the stored number.

[shg]

...deleted, hang on...

[shg]

Here we go:

	A	B
1	…
2	4	A2: =MOD(SUM(A1, RANDBETWEEN(0, 3)), 5) + 1
3	5
4	2
5	3
6	4
7	5
8	2
9	1
10	4
11	3
12	1
13	2
14	4
15	1
16	3
17	4
18	1
19	3
20	5
21	4

[HJay]

Thank you shg! That works perfectly and such a 'seemingly' simple formula. I would love to understand it fully.

So MOD works out the remainder of two numbers, which is (SUM(A1, RANDBETWEEN(0, 3)) and 5.
(SUM(A1, RANDBETWEEN(0, 3)) this adds the previous random number to another random number between 0 and 3. Why 0?
And then you add a 1 at the end. Is this something to do with not ending up with a 0?

If you have time to explain I'd really like to further my knowledge.

Many thanks anyway

[HJay]

...deleted, managed to duplicate post?

[shg]

You're welcome.

Consider the numbers as a ring: 1,2,3,4,5,1,2,3,4,5,... For any given number, the formula randomly chooses one of the four that follow.

It would be simpler if the numbers cycled 0 to 4:

	A	B
1	…
2	1	A2: =MOD(SUM(A1, RANDBETWEEN(1, 4)), 5)
3	0
4	2
5	0
6	1
7	4
8	1
9	0
10	1
11	0
12	2
13	3

The difference between that and the other formula just caters to the sequence being 1-based instead of 0-based.

[HJay]

So it would be better if the RANDBETWEEN in A1 was RANDBETWEEN(0,4) rather than RANDBETWEEN(1,5) ?

Currently trying to create a similar formula for 12 pictures this seems to be working

=MOD(SUM(A1, RANDBETWEEN(0, 10)), 12) + 1

Is this correct or just an accident? All I have done is make the 2nd number of the RANDBETWEEN two less than the total number of pictures but I don't know why.

I have just tried setting A1 to RANDBETWEEN(0,11)
and A2 to =MOD(SUM(A2, RANDBETWEEN(0, 10)), 11) but I get duplicates with this.

Sorry I may be having a maths problem rather than an excel problem here