Rank Formula

[c0087]

1	4		4
2	5
3	6
4	1
5	2
6	3

D1 will display a random number between 1 and 6 - in this example 4. Whichever number is displayed in D1 needs to be ranked as 1 in column B, and then the following ranked accordingly.

If 2 were to be displayed, then the following would like this, etc.

1	6		2
2	1
3	2
4	3
5	4
6	5

 

[myall_blues]

In B1 put:

=IF(A1>=D$1,A1-D$1+1,ABS(D$1-7-A1))

and copy down.