Spreadsheet Magic: Randomizing Blogs

I was not sure if I should write up notes on how to build a randomizer in a spreadsheet, so I asked a few people and concluded that, yes, writing up some notes might be a good thing! So, here are some notes about how I create randomizers for my classes in a GoogleDocs spreadsheet. 

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.

SPREADSHEET STRATEGIES

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:

=RAND()

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:

=HYPERLINK(url, label)

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.

BLOG RANDOMIZER

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

When I have some time to comment, I randomize the spreadsheet by sorting the random column A-Z. Then, I comment on as many blogs as I have time for. When I comment, I write "commented" in the commented column and I blank out the random column.

Don't re-sort after  you comment on each blog; just work on down through the list commenting. The random numbers will regenerate every time you edit the spreadsheet, but that doesn't matter.

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:

Pretty nifty, yes? Basically zero time spent keeping track of who got comments from you, so you can spend all your time on the commenting itself!

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.

GROUP RANDOMIZER

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.

The idea is that I can use the randomizing column to sort, and then I paste in the group numbers from a separate sheet (1-1-1-2-2-2-3-3-3 for three people in each group). That allows me to copy the group number column with the blog link column to create the actual blog groups. Then, I come back to the spreadsheet, sort on the name column, and that allows me to create the alphabetical list of names showing each person their group number. You can see the results here: Sample Blog Groups.

For the project commenting groups, it's the same procedure although a bit more complicated for reasons just having to do with my classes (some students do Portfolios, some do Storybooks, etc. etc.), but I use a spreadsheet to create the random groups in just the same way!


Screencast

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: