Generating (in a random order)each number once from a given number

[Neil Goldwasser]

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

 

[Peo Sjoblom]

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 (edit>copy or ctrl + C) E1:E11, while still
selected do edit>paste 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

 

[Neil Goldwasser]

That's great! Thanks Peo, it has really helped.

Just one quick question though:
Is there a way of re-generating the numbers again on request, e.g. with a
button or something, so that once the students had completed the first set of
12 questions, they could get the same times table questions, but in a
different order?

If it is possible to have a non-macro way of recalculating the formulae
manually (i.e. when they choose to generate a new set of questions rather
than the random function happening every time a new cell value is given) then
this would be best, but if it has to be a macro then I have picked up some
knowledge of these (thank you again to Norman Jones for kick-starting my
education in this area, just in case you are reading this!).

Also, is it possible to clear cells to give blank cells when I enter a
particular value somewhere or press a button etc...? Ideally I'd like the
students to complete the first set of 12 times table question, press button A
to clear their answer cells, press button B to generate the questions in a
new random order, and then answer them all over again.

I apologise in advance if this sounds like I'm being picky but these
students will really benefit from practising each times table several times
over, and if they practised the same random order over and over again the
danger is that they'll learn an incorrect number sequence (e.g. thinking that
in the three times table 21 will always follow 6 or something horrendously
misleading like that). I work in Learning Support so I have to make sure to
avoid such pitfalls.

Again, thanks for your help so far, Neil