# Answering question with Random Function

1. ## Answering question with Random Function

Hi all - newcomer here, so be gentle with me!

I have a question - I'm creating a test with excel. In one tab i have a list of values grouped together (just so happens to be a list of German verbs), and in another tab I have a formula that allows one of those cells to be chosen at random (using fn f9 on mac). Formula is

=INDEX('xxx.xlsx'!name, RANDBETWEEN(2, 187)) where xxx is the name of the sheet, name is the group name, and 2 and 187 are the cell numbers at the beginning and end of the list.

However what I would ideally like is to be able to enter an answer (in my example the english translation of the german verb randomised), and the sheet to be able to indicate if I am right or wrong (maybe by turning the cell green or red)... but I have no idea how to do this.

Any help gratefully received! Many thanks.

2. ## Re: Answering question with Random Function

Conceptually, this could work with a formula like this:

=IF(E2=INDEX(B2:B4,MATCH(D2,A2:A4,0)),"CORRECT", "Incorrect, "&D2&" means "&INDEX(B2:B4,MATCH(D2,A2:A4,0)))

However, you've got an inherent problem in your worksheet that will prevent this from working:

But, at the same time, it will also re-run the calculation of the randomly selected verb.

Which means that even if your answer was right, it won't be right anymore after Excel recalculates...

See attached for a rough demo.

The problem is in the way that Randbetween() automatically recalculates each time the spreadsheet updates...

3. ## Re: Answering question with Random Function

Bagooseuk,

Attached does what you want.

Sheet 1 has list in COl A (in this example, your verbs).

Cols B - E then has possible answers, set as "ranges" under the Verb name.

For each verb, decide which answer is the "right" one.

Sheet 2 lists the verbs in Col B.

Against each one is a DropBox in Col C conditionally formatted as cell value containing whichever value you set as the "right" answer to the question (e.g. Sheet1!\$B\$2, or Sheet1!\$D\$3)

So for each question, the student opens DropBox and has choice of answers, If he picks the one you set, cell goes green, Otherwise cell stays white.

I have createed the link for Qs 1 - 3.

Ochimus

4. ## Re: Answering question with Random Function

Thanks Eibi! That's helped a lot, and I'm very close now. The problem I see with the formula you provided is that as soon as you enter the word, the randomisation happens again, and it answers based on the new random word, not the one that was there when you started typing. How can I stop it randomising until it answers the question? See attached - please see the "German Verb" tab.

*edit* - I notice now you stated exactly that in your post. Does anyone know how I can overcome this?

5. ## Re: Answering question with Random Function

ooh, Ochimus - thank you. That's really useful and a very cool function and, as you say, great for testing students. But I'm initially building a tool for myself! I think in what I am trying to do, the dropdown box of like a multiple-choice test is not what I'm after...but I like the colour change function, and may try to build this in! Thank you for your efforts, however I think I'd really like to pursue Elbi's version in this instance.

6. ## Re: Answering question with Random Function

Here's my suggestion:

Move the Random formula to your source spreadsheet. Then, on the quiz sheet, simply refer to the random formula with

='Source Sheet'!D2

Now -- when you press F9, you get a new random word. After you enter your answer, press SHIFT+F9....to calculate the sheet only.

(This way, the Random formula on the other sheet does not update.)

F9 -> present a new word

See attached.

7. ## Re: Answering question with Random Function

Eibi - I've downloaded your file (2) and the test sheet still randomises after typing an answer, even though you have ='Source Sheet'!D2 entered... am I missing something... I am using a mac, so normal F9 function I have to press fn-F9... I have no idea what I need to press to get the shift-F9 equivalent

8. ## Re: Answering question with Random Function

Problem 1: Automatic recalculation.

You have a setting in the background (everybody does -- not just mac computers) that tells Excel to calculate every time you change the contents of a cell. This causes the randomization function to re-calculate every time you enter an answer in your spreadsheet...

You want to turn that option OFF.

Click File ->Options -> Formulas ....and change Workbook Calculation from Automatic to Manual

Then OK out.

NOTE: This will apply to all your excel files, so when you're done with your german verbs, go turn the calculations back to automatic !!!

Problem 2: Equivalent for Shift+F9.

I assume that this "Calculate Sheet Only" key combination is the same on the Mac, so it might be resolved after you solve Problem 1, above. If not, you'll find the "Calculate Sheet" command on your Formulas Ribbon in the Calculation command group. You can hover your mouse over the "Calculate Sheet" icon, and it will probably tell you the keyboard shortcut for the command. Mine says (Shift+F9). I don't know what yours might say.

9. ## Re: Answering question with Random Function

Problem 1 - yes I discovered this ability to change from automatic to manual already - and the problem means that If I turn this off, not only will the verb not randomise, also the answer (correct or incorrect) won't change when you type in the answer - it's an all or nothing, you either have all formulas automatically calculated, or all manual - you can't seem to have it so the answering is automatic, but the verb change is manual.

And, if I have it as manual, when I type the answer in and then try to make the answer box change, it also changes the Verb, and it seems to affect all tabs in the excel sheet...

Seems I'm stuck...

10. ## Re: Answering question with Random Function

It's not exactly "all or nothing". When you change to Manual, you have the option to -

Calculate nothing.

OR

Calculate workbook manually (F9).

OR

Calculate a single sheet of the workbook manually (Shift+F9).

By moving the Random() function to another sheet, you should be able to now use the Calculate Sheet command to update the "correct/incorrect" response without updating the random() function. Perhaps the operation is slightly different on a mac, as you have suggested; but I think the principle should work if it is worth the time to look for the solution.

It's more fun than writing up 187 flashcards, right?

All best...

11. ## Re: Answering question with Random Function

Much more fun!

Amazing - we've solved it, and I now have a functioning test! Thanks to all. Please close the thread!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1