Thursday, July 30, 2015

Randomizing an array of text in an xlsx document

I had a list of statements in a likert test for test administrators:

But I didn't want the respondents (after a hard day administering test in a busy school) to go onto autopilot, if possible, so I needed a quick way to randomise the statements - the sort function will only sort them alphabetically, ascending or descending.

No problem, once I'd worked it out.

Get the RAND function on a cell in the row of the first cell:


Drag the formula down the column adjoining your column of text, and it gives each row a genuinely random number:


Now sort that column, doesn't matter ascending or descending, expanding the sort to include your column of text. Just delete your column of random numbers. And voila, a now truly random set of statements for your likert. Easy-peasy.