A Random Challenge

[Lewiy]

Here’s a little challenge for everyone:

Object
Enter formula(s) in each of 6 cells A1:F1 such that when the worksheet is calculated it produces a random integer between 0 and 9 in each cell with no repeats.

Rules
Formulas only, no macros/vba
Shortest formula wins (total number of characters over all 6 cells)
Random numbers must be generated equally over a large number of trials

Good Luck!

 

[SteveO59L]

In A1

=RANDBETWEEN(0,3)

B1

=+A1+1

C1

=b1+1
etc

 

[Lewiy]

Not very random Steve, 9 never appears and 3,4 & 5 appear every time.

 

[Jubjab]

in A1:

=round(rand()*10;0)-1

in b1 and copied right:

=if(a1=9;0;a1+1)

That's 20 + 15*5 = 95 characters. All values are random, are not repeated, and occur with the same freqency.

 

[Jubjab]

If analysis toolpak is allowed, using randbetween shortens my formula a little bit.

 

[Lewiy]

Jubjab, that certainly does the trick in terms of frequency, however, it does mean that you get a sequence of consecutive numbers each time which in itself does not produce 6 independently-random-non-repeated numbers of which there are 151,200 combinations, but rather one of ten possible sequences.

 

[Jonmo1]

Independantly random was not a requirement in the original post. And Non Sequencial Was Not a requirement either.

 

[barry houdini]

You can do this with a helper row, i.e. put this formula in A2

=RAND()

copy across to J2

In A1

=RANK(A2,$A2:$J2)-1

copy across to F1

hide row 2 if you wish

Or......you can accomplish this with a single (rather long) formula, see pgc01's suggestion here

 

[barry houdini]

=round(rand()*10;0)-1

This wouldn't be a good way to generate a random number between 0 and 9. It'll generate some -1s and fewer 9s than other numbers. To generate 0 to 9 with the same frequency

=INT(RAND()*10)

 

[Lewiy]

Jonmo, I appreciate that the “randomness” was perhaps not fully defined in the original post, but I did ask that a random integer was produced in each cell, jubjab’s solution does not produce a random number in each cell it only produces 1 random number in A1 with all the other cells explicitly dependant on it. Thus you can precisely “predict” the results in cells B1:F1 based on A1 making it non-random.

Barry, your solution only produces values from 0-5.