Part of my role as a PhD student is helping out undergraduates with problems. This is a lot less formal than a lecturer’s office hours, and works on a first-come, first-served basis. Unfortunately, this means that there’s a lot of time taken out of my own work and spent on others. I don’t see this as a complaint, as a lot of the issues are actually extremely interesting to deal with. However it’s important to make you, as a reader, aware because most of these problems are actually entirely preventable. And that is a complaint.

Allow me to set the scene. We’re about half-way through the day, I’m sat in the lab and an undergrad asks if I can help them work through an output file for their project. I agree, and pop open their worksheet. It’s a bit cluttered, but everyone has their own method of organisation… It’s not to my taste of sorting things, if that’s a better way to put it. There are a lot of things missing here, though. Big things, in fact. Namely, the norms for stimuli used in the experiment. This is problematic, especially because the individual was wanting to analyse both items and trials. Not having the norms attached to words isn’t an issue within itself, so long as each of the conditions are normed in advance of the study. With the approach adopted by the undergrad, this wasn’t the case.

Debates as to the efficacy of this aside, what I was now faced with was an excel sheet totaling near ~4000 columns, each needing a set of norms for a prime to be attached to it. Namely, means for familiarity and age of acquisition. Standard stuff, but I was faced with having to manually enter the data. Bollocks to that. Cue excel wizardry! What initially seemed like hours of effort instead totalled about 45 minutes – including the time taken for planning.

The formula I came up with works quite simply as a two step process involving, firstly, a match function followed up with, secondly, indirect and concatenate commands. Before I get onto showing my working, it’s important to understand what these three things do:

  • Match – “searches for a specified item in an array of cells, and returns the relative position of that item” – example: =match(A1,B1:B25,0). The first argument (A1) is the item you’re wanting to look up, the second (B1:B25) is the array you’re wanting the lookup to be performed in and the last (0) is the lookup argument. The lookup argument, in this case, reflects an exact match, i.e. “find only the contents of A1, ignore everything else. For numerical values, the argument may also be less than or greater than – something not to be concerned about when matching text.
  • Indirect – “returns the reference specified by a text string. Indirect changes the reference to a cell within a formula without changing the formula itself” – example: =indirect(x,A1) where x is the reference.
  • Concatenate – “a function that joins text into a string” – example: =concatenate(A1,B1,C1)

So perhaps you can see where I’m going with this. If not, then maybe give it a go before reading my explanation. See if you can come up with the answer yourself.  ^_^


Kicking the process off, the match function returns a numerical value equal to an item’s relative location in a list. This means that so long as the list begins at row 1, the value returned will be equal to the position of the item being searched for. In other words, the match command is returning the row number from an array. Next, concatenate allows us to join strings of text together, be it from a given cell (e.g. A1) or text (“banana”). When used with the indirect command, a user can change reference to a cell without altering a formula.

And that’s it! All 4000 rows were done this way.  Firstly, for each trial, match returns the word’s location in a priming database:


It’s important to use the relative position of an array so that when it’s applied to the whole database the location of the array doesn’t change (and it would, if you didn’t use relative links).

Secondly, using indirect and concatenate allowed the combination of the column the norm is in, and the result of the match command:


The output of this, of course, will be “T[result of the match command]” which is then interpreted by the indirect command to display the contents of the cell instead of the actual cell location.

But the important part here isn’t the way that I managed to get around it, instead that the problem could have been solved in the first place if the student had planned properly. This is much easier said than done, and I probably ended up thinking for longer about how I could learn from my own lesson and plan better than I did thinking of the solution.  See, for the most part, proper planning isn’t so much something that can be taught as it is something that’s developed. I didn’t have the luxury of someone to help solve my faults as an undergrad; perhaps helping others is hindering them because they’re relying on other people. I don’t know.

To the latter, at least, it’s reassuring to know that some students will have a hack at it by themselves first. They’ll have tried to solve the issue and, in most cases, had a pretty good go at it. Not all students halt at the first hurdle. These are the ones that are likely to learn from any help that’s offered, as they’ll study the outcome and figure out how to do it. Unfortunately, it’s my experience that this camp is in the minority. And so we’re left with the issue of planning.

There’s only so much I can say about the way my university teaches lab skills on something that’s so openly attributable to me, but the issue really lies in the pipeline problem. Throughout education, from nursery to college, students aren’t taught to be independent workers, but instead learn the best strategy to attack exams. Of course, because the idea that exams define the rest of an individual’s life is stressed so heavily, you can’t blame the buggers for getting on board with that. The problem arises when that curtain’s removed from the window of life and outside, instead of exams, are rolling hills. Rolling hills that are there to be climbed at one’s own pace, with the minimum of help from other people. After all, they are your own legs. Why should they be moved for you?

The only solution I’ve offered is practical: how to get by with a bodged output. There is no solution to independence, other than that you develop yourself.

Leave a Reply

Your email address will not be published. Required fields are marked *


1 2
June 20th, 2016

Student-Led Teaching Award Nominations

June 15th, 2016

My Stance On The EU Referendum

April 28th, 2016


January 17th, 2016

Finding an outlet: there’s more to life than a PhD

April 8th, 2015

Database-fu, and the zen of independence

March 11th, 2015

Still here.

July 10th, 2014


May 8th, 2014

On Working

February 28th, 2014

Life Lessons Learned From Foxit Reader

July 18th, 2013

A Complaint To Northern Rail.