Generating (in a random order)each number once from a given number
Put 1 in D1, 2 in D2 and copy down to D11, that will give you
1
2
3
4
etc
in E1 put
=RAND()
copy down to E11
Now if you don't want these numbers to change every time you calculate
the sheet, select and copy (editcopy or ctrl + C) E1:E11, while still
selected do editpaste special and select values, now in A1 put
=INDEX($D$1:$D$11,RANK(E1,$E$1:$E$11))
copy down to A11
if you don't copy and paste special the RAND() values will change each time
the sheet is calculated thus the values in A1:A11 will change as well but if
you copy the RAND range and paste special as values it will be fixed
--
Regards,
Peo Sjoblom
"Neil Goldwasser" wrote in
message ...
I am trying to make a self-marking interactive times table test for my
students.
The aim is that they can choose the times table by entering a number into
one cell, and Excel will generate a set of questions to match. These need
to
be in a random order so as to test rote memory rather than the sequence of
that times table. So
7 x 3
11 x 3 etc...
rather than
1 x 3
2 x 3 etc...
I have made my sheet, and I just need to rejig it so that the first number
in the above calculations is 1) randomly generated and 2) is only
generated
once (I want them to test up to 12 x ..., doing each question once only
(so
do not want, for example 2 x 3 coming up twice as this would mean the
second
one takes up the place of the question that will be "missing").
So I need the first cell e.g. A1 to randomly generate (with fair
probability) a number from the set {1,2,3,4,5,6,7,8,9,10,11,12} so that
the
line will form e.g. 7 x 3.
The second cell A2 can't duplicate, so would need to pick randomly from
the
set {1,2,3,4,5,6,8,9,10,11,12} - so that it now cannot pick the number 7
that
has already come up.
And so on and so on until the last cell A12 can only pick the last
remaining
number that has not already been generated.
Ideally, I would like the numbers to then remain fixed so that the sheet
can
mark what they have done as right or wrong, and only generate a new set of
12
questions once a button is pressed, or a different value is entered
somewhere
or whatever (as long as they choose when it "recalculates" and Excel
doesn't
do it every time they enter an answer).
Does anybody have a solution for this teacher who is desperately trying to
do what he can for his students? I'd be really grateful!
Thanks to everyone in advance, Neil Goldwasser
|