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!

 

[pgc01]

HI Lewiy

In A1:

=SMALL(IF(COUNTIF(B1:$G$1,ROW($1:$10)-1)<>1,ROW($1:$10)-1),1+INT(RAND()*(10+COLUMN()-COLUMN($G$1))))

This in an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy right till F1

Leave G1 empty

Kind regards
PGC

 

[barry houdini]

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

Not, if correctly implemented. As stated above, you have to copy =RAND() in A2 across to J2


For a single formula solution, using pgc01's formula from the link I posted above, shortened as far as I can......

in A1

=SMALL(IF(COUNTIF(B1:$G1,ROW(1:10)-1)=0,ROW(1:10)-1),1+INT(RAND()*(COLUMN()+4)))

confirm with CTRL+SHIFT+ENTER, copy across to F1, G1 should be blank

 

[pgc01]

Hi again

Barry's solution works OK for me.

PGC

 

[Jonmo1]

if B is dependant on A and A is random, then B is also therfore random.

 

[Lewiy]

But B is not a random number in it's own right, it is always 1 more than A

 

[pgc01]

Hi Lewiy

I have an error in my formula, it generates only the numbers 0-8. However there is no need for me to correct it, since I see that Barry already posted a perfect formulation of the formula.

Thanks Barry.

Kind regards
PGC

 

[Lewiy]

Thanks PGC and Barry, I think together you've cracked this one. Good job!