DIY personalized, randomized assignments

I like giving students randomized assignments. That is, I like assignments where they all get the same questions, but different numbers. That way, they can collaborate, but have to ask “how did you solve that question?” rather than “what did you get for that question?

Years ago I tried out Webassign, and I liked the way it built algorithm-based questions. But I don’t have it, and I don’t have money in the budget to pay for it. We use Blackboard as our LMS, and it too has algorithm questions, but the format is kludgy at best. It uses a Java based visual equation editor, which has issues with parsing complex equations, rather than plain text entry. It is so cumbersome I gave up on it. I have Wiley Plus with my senior Physics textbook, which is great, but it doesn’t help with any of my other subjects.

In terms of online resources, I am short of easy and cheap options. But who said it had to be online? I have plenty of tools available right on my laptop that will do the job just fine! Here is how I create personalized, randomized numerical assignments for my students:

Basically, I create a set of questions, then use a spreadsheet to generate random numbers and solutions for those questions. Mail merge tools can then be used to import the randomized numbers into the questions. I use Excel and Word, but any spreadsheet and word processor should do the job.

Let’s step through the process, examine the formulas, and then you can make your very own! To begin, we need a question. For this demo, we will pick something fairly simple. Normally I include 5 or 6 questions in this type of assignment, but we will use one to illustrate. How about

Three light bulbs are connected in parallel.  The first has a resistance of [R1]Ω, the second has a resistance of [R2]Ω and the third has a resistance of [R3]Ω.  The total voltage for the power supply is [V]V. What is the current in the circuit?

Now we have a question, we need to set up our spreadsheet. I set mine up like this:

Then I decide on my max and min values, and enter them.

Then comes the magic:

To explain briefly, this formula grabs the max and min values, and generates a random number between the two, and rounds that number to the number of decimals required. The dollar signs in front of the cell numbers indicate that the formula must always grab those top three numbers for calculating, that way I can copy the formula anywhere, and it will always work.  Here is what it looks like copied to the other cells:

Note that the column (letter) changes in the formula, but the row (number) remains the same. That’s the dollar sign at work.

Next we need a formula for the answer:

Now we are almost ready, but there is one last step. The RAND() function will regenerate random numbers every time the spreadsheet is opened, and every time you modify a cell (note how the values are different in the last two images). So if you want a static set of numbers that will remain the same no matter how many times it is accessed, you need to create one. I copy the excel table (minus the top three rows) and paste it into a word document. Really, any type of document would do, Word can use a spreadsheet, text file, word document, database, or just about anything as a data source.



Now we turn our original question(s) document into a mail merge, define the static data table as the mail merge source, and insert the merge fields for the placeholders we left in the question.  We can even insert the student’s name, to personalize it:

And for me, I use a separate mail merge document to generate a table of answers for each student into a master answer sheet:

(For organization purposes, I tend to name my files similarly, like AssignmentX.doc, AssignmentXnumbers.xls, AssignmentXstaticNumbers.doc, AssignmentXanswersheet.doc, and place them in a folder of their own. )

On the assignment sheet itself, I usually include two rows of answer boxes, labeled “1st try” and “2nd try”. If my goal is to get students to understand how to solve these problems, I want them to retry any questions they get wrong. Having answer boxes on the sheet means scoring (marking, grading, whatever term you like) the assignments is easy – they hand me the assignment, I check the answers against my master answer sheet, record their score on my sheet, and hand it back. Maybe 20 seconds per student.

These assignments have a lot going for them – they allow students to collaborate, they offer immediate feedback, they allow students to correct – or at least attempt to correct – their mistakes, they are kind of fun to make, and they are dead easy to mark (grade, score, whatever…). There is some up-front time creating them, but they save loads of time at the other end, and once you have them, year after year you can just drop in the names of your new students, and prep time is minimal. It is used offline – no risk of network issues – and it is free. Even if you don’t have Word and Excel, you can do the same thing in OpenOffice, which is free. So all around, this type of assignment has lots going for it. Really, the only shortcoming is that these are not open-ended inquiry assignments – but then, they are not intended to be the only type of assessment, just one of many.

Try it, and let me know how it works out. Also, if there are any GoogleDoc wizards out there who might know how to create an online version, I would love to hear from you!

13 thoughts on “DIY personalized, randomized assignments

  1. Pingback: Tweets that mention DIY personalized, randomized assignments | Teach Science (.net) --

  2. Anthony Perez

    Your article show how to print one set of question; one for each student. Is it possible to print several different questions on the same student page? Say, one question to find the V in a parallel circuit, another on find the power in a different circuit, a third dealing with a series circuit.

    Then each student gets 3 different questions on his/her paper.

    Hope I have made myself clear.

    Tony Perez

    1. ed Post author

      Certainly – I just used a single question to keep the explanation simple.
      Just keep going – in this example, you would start putting variables for the second question in column G, H and I (or however many you need), a formula for the answer in J, and the variables for question 3 in K and L, and so on. I name my variables by question (in this case Q1R1, Q1R2 etc), so the next set would be, say, Q2R1, Q2R1 etc.
      I typically have 5 questions on one of these assignments. Let me know how it turns out!

  3. Allan

    Any ideas on good resources for using more complex excel formulas, trying to use this to generate a set of questions on Gravitation and the numbers I am getting aren’t correct. Clearly my skills at Excel need work, for example R=(GMT*T/(4*pi^2))^1/3

  4. ed Post author

    If you Google “excel function tutorial” you can find lots of resources for learning. The main thing to watch out for when putting together a formula is the order of operations. For example, if you type =27^1/3 it will give you 9 as the answer, because it is calculating (27^1)/3. Brackets are useful, sometimes I use them even when they aren’t strictly necessary just to make it clear. So you would enter =27^(1/3).
    If I wanted to calculate an orbital radius given the period (T), I might do it like this:
    If T is stored in cell D4, then my formula would be:
    the value of pi is a built in function, and needs empty brackets. That makes perfect sense to programmers, and is completely stupid to anyone else. the E is used as an exponent of 10, so instead of 6.67*10^(-11), we can write 6.67E-11, just like on a scientific calculator.
    I hope that helps!

  5. Joe Morin

    I love the idea and the method. But (I teach 4 sections of physics) how would you deal with the logistics of printing and distributing 100 individualized worksheets?


    1. ed Post author

      I sort the students in the master list by class, so I have section A listed alphabetically, then B, etc. From within the mail-merge tools you can print all records, or just a selected range. Then just hand out the copies to each student in each class.
      Alternatively, you could just assign a serial number to each one, instead of a name, so it really doesn’t mater who gets which one. Then when students hand it back you just look up the number rather than the name. But I like the personal touch of the name better.

      1. Joe Morin

        Okay. This puts a heavy demand on the printer toner, but it might be okay once and awile for one page (double sided) worksheets. Hmm … our copier is networked. If I could figure out how to send over the network to the copier I could get multipage, stapled individualized worksheets. How do you do this?

        I like the personal touch also. Thanks.

        1. ed Post author

          Our copier is networked as well, it is installed as a network printer in Windows. Is there a tech support person who can help you install the drivers?
          On a small printer, yes, it would be a pain.

  6. Pingback: Happy bloggiversary to me | Teach Science (.net)

  7. Pingback: Physics Teacher - Randomized Homework Assignments

  8. Pingback: #5: Joss Ives: Two-stage quizzes (4/13/12) | Global Physics Department

Leave a Reply