I use the spreadsheet randomizer for two purposes: to randomize my commenting at the students' blogs and to randomize the commenting group assignments for both blogs and projects. I'm guessing this is potentially useful for anyone running a class with a blog network, especially if (like me) you are short on time without a lot of time to comment on student blogs but want to do so fairly and/or if (like me) you want to get students introduced to as many other students as possible in the class by randomizing the comment groups each week.
I am going to explain how these concepts work in GoogleDocs spreadsheets. Presumably they work in Excel also, but you'll have to check and see. If you use Excel and have never tried GoogleDocs spreadsheets, why not use this as an excuse to give the GoogleDocs version a try...? Being able to access your spreadsheets on any computer sure is handy!
Here are a few key strategies to note:
Random function. It's easy to insert the random function in a GoogleDocs spreadsheet cell. Just type this in the cell:
You can then sort on that column and it will randomize all the rows. Trust me on this. It will look weird because after the sort, the spreadsheet randomly assigns values to the cells (after having sorted on the previous set of random values). But since that is after the sort, it's all good. If you don't believe me, pound away on a column for a few tries: sort A-Z, then sort again, then sort again. You will see the magic of randomness at work!
Hyperlink function. When you are first setting up your spreadsheet, you will probably want to use the hyperlink function to create the actual linked text. I create a column with student names, for example, and a column with their blog URLs. Then I use the hyperlink function to create the linked text. The function is:
I then do a copy of the column and paste-special-values-only because I don't really like live formulas lying around if I don't need them. Then I hide the columns with the names and the blog URLs; I can use them for other things later, but I don't need them anymore for the randomizer now that I have the linked text.
Sheets. I have met some people who weren't confident about using separate sub-sheets in a spreadsheet. Use sheets! I have one spreadsheet for my randomizers, and it has six sheets. I use the '3groups' data to help manage the IEblogs and the MFblogs as you will see below; having it all in one big spreadsheet is great.
Freeze header row. You may think you don't need it, but it's really helpful to create a header row and freeze it so that you always know for sure what you are dealing with in your spreadsheet. I colorcode my header rows for my different classes to help remind me just what I am looking at (purple for both classes, yellow for Myth-Folklore, green for Indian Epics).
Okay, with those preliminaries out of the way, here's how I randomize blogs that I look at, and here's how I randomize blog comment groups for the students.
For the blog randomizer, I need three columns:
- random column: see RAND function above
- commented column: blank or "commented"
- blog link: see HYPERLINK function above
Then, the next time that you sort the random column, all the commented blogs will go to the bottom of the list because the random column is blank. Here's what the bottom of the list looks like after I sort the random column next time:
After you have cycled through all the students (and honestly, it takes me a few weeks; my focus is on commenting on their projects, not on their blogs), you can then type RAND() in the cells of the random column and start all over again.
For the group randomizer, only one thing is different: I need a column for the group number. So, there are now four columns in the spreadsheet: the randomizing column, name, GROUP, and the blog link.
If you would like a screencast demo, check out the screencast I made for our DML2015 panel: Laura Loves Randomizers! The part about using spreadsheets to randomize starts at about 4:30 in the video: