Rand and Randbetween Question

Question

I'm trying to build a simple tool in Excel that will help my son learn his multiplication tables. I want to be able to randomly generate questions, allow him to answer the question and then let him know if his answer is right or wrong.

I've created a table of all possible questions per the below.  I was going to use the formula =vlookup(randbetween(1,13),A1:C13,2,False)) to generate a random question for my son to answer.  The problem is when the answer is entered, the randbetween formula immediately recalculates, so the question changes before my son can see if his answer was right.  Is there a way that I can freeze the randbetween formula so that it doesn't generate a new question until after my son enters his answer and knows if the answer is right or wrong?  Ideally, I'd like to be able to freeze the question until he gets the answer right, and allow him to control when the new question is generated.  I'm using an if formula that will tell him if his answer matches the correct answer.  Any ideas on how to do this?   

	A	B	C
1	1	1 X 0 =	0
2	2	1 X 1 =	1
3	3	1 X 2 =	2
4	4	1 X 3 =	3
5	5	1 X 4 =	4
6	6	1 X 5 =	5
7	7	1 X 6 =	6
8	8	1 X 7 =	7
9	9	1 X 8 =	8
10	10	1 X 9 =	9
11	11	1 X 10 =	10
12	12	1 X 11 =	11
13	13	1 X 12 =	12

Answer 1

Switch off the Auto Calculation Mode by Formulas tab > Calculation Options > Manual

Now, the randbetween will not change when you press enter.

If you enter randbetween formula in any cell and enter, no other cell will change. If you want to change only the cell where randbetween is entered, just press F2 in that cell and press enter, that cell will recalculate itself and no other cell will recalculate.

When your task is over, revert to Auto Calculation Mode (If you want to save your answers, you can copy and paste special as values before reverting to Auto Calculation Mode)

---------------------------------EDIT-----------------------------------------------

If you are open for VBA solution, then I have found a way at following link -

http://datapigtechnologies.com/blog/index.php/stupid-random-numbers-keep-recalculating/

You need to follow following steps -

1. Save your Workbook as .xlsm
2. ALT+F11
3. Insert > Module
4. Copy following code ans Save.

Function RandomBetween(Low As Long, High As Long)

            Randomize

            RandomBetween = Int(Rnd * (High + 1 - Low)) + Low

End Function

5. Now, in your worksheet, you can put following code

= RandomBetween(1,13)

It will generate a random number every time without changing previous one.

Answer 2

Hello,

Here is a test generator I created some time ago:

https://dl.dropboxusercontent.com/u/6077606/Generate\_Math\_Test.xlsm

Download and use at your own risk.

Regards,

Bernd