+ Reply to Thread
Results 1 to 11 of 11

Answering question with Random Function

  1. #1
    Registered User
    Join Date
    03-25-2016
    Location
    Berlin
    MS-Off Ver
    Mac 14.5.1
    Posts
    10

    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. #2
    Registered User
    Join Date
    01-08-2014
    Location
    Missouri
    MS-Off Ver
    365
    Posts
    87

    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:

    When you enter your answer, excel will have to calculate the spreadsheet in order to tell you if your answer is right.

    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...
    Attached Files Attached Files
    Last edited by eibi; 03-25-2016 at 11:26 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,308

    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
    Attached Files Attached Files
    Last edited by Ochimus; 03-25-2016 at 11:37 AM.

  4. #4
    Registered User
    Join Date
    03-25-2016
    Location
    Berlin
    MS-Off Ver
    Mac 14.5.1
    Posts
    10

    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?
    Last edited by Bagooseuk; 03-25-2016 at 01:38 PM.

  5. #5
    Registered User
    Join Date
    03-25-2016
    Location
    Berlin
    MS-Off Ver
    Mac 14.5.1
    Posts
    10

    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. #6
    Registered User
    Join Date
    01-08-2014
    Location
    Missouri
    MS-Off Ver
    365
    Posts
    87

    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
    Shift+F9 -> check my answer

    See attached.
    Attached Files Attached Files
    Last edited by eibi; 03-25-2016 at 12:36 PM.

  7. #7
    Registered User
    Join Date
    03-25-2016
    Location
    Berlin
    MS-Off Ver
    Mac 14.5.1
    Posts
    10

    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. #8
    Registered User
    Join Date
    01-08-2014
    Location
    Missouri
    MS-Off Ver
    365
    Posts
    87

    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. #9
    Registered User
    Join Date
    03-25-2016
    Location
    Berlin
    MS-Off Ver
    Mac 14.5.1
    Posts
    10

    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. #10
    Registered User
    Join Date
    01-08-2014
    Location
    Missouri
    MS-Off Ver
    365
    Posts
    87

    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...
    Last edited by eibi; 03-25-2016 at 12:49 PM.

  11. #11
    Registered User
    Join Date
    03-25-2016
    Location
    Berlin
    MS-Off Ver
    Mac 14.5.1
    Posts
    10

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Answering a question in a cell
    By Salesmaster in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  2. Answering a question in a cell
    By Biff in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] Answering a question in a cell
    By Biff in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] Answering a question in a cell
    By Salesmaster in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. Answering a question in a cell
    By Salesmaster in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. [SOLVED] Answering a question in a cell
    By Salesmaster in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Answering a question in a cell
    By Salesmaster in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. Answering a question in a cell
    By Salesmaster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2005, 06:05 PM

Bookmarks

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