The circle of (academic) life

Don’t worry, there’s not going to be any lions getting trampled. Though it would be nice to have a group sing-song and coordinated dance number.

Mother and baby bird, my fire escape, Richmond, VA, 2011

I thought the title of this might be “the passing down of academic wisdom”, but that sounds a little high-faluting… What I actually am going to write about is a couple of data-related things that I learnt from my PhD supervisor that have stuck with me, and on occasion I am now starting to be able to pass them on to other students who are earlier in their academic careers. This gives me a warm fuzzy feeling.

Two of the most helpful data-sorting tools I was taught to use during my PhD were how to create macros and if-then formulas in Excel.

Macros are useful if you have some action or task you want to perform repeatedly in a spreadsheet.  For example, if you need to sort each of your participants data by some variable, or calculate a sum for some subset of data for each participant (and these tasks cannot be performed by a simple sum-fill-down kinda function).  Basically macros are like a little ‘video recording’.  You record yourself performing the action on one participant’s data – paying careful attention to which cell was selected at the start and end of your recording – and then you ‘run’ that recorded macro on each of your other participant’s data in turn.  Instead of it taking hours for you to manually sort/copy/paste/run a function multiple times, you can just press the ctrl+(whatever key you assigned to that macro) once for each participant and your macro will calculate the required data.

Macros can be kinda tricky, and you have to be careful when recording them – particularly if you are working across different sheets of data.  But if you make a mistake you can just open up the macro recording and correct it… Or record the macro again.

I’m sure I don’t use macros to their full capacity, and I don’t know how to write VBA script to construct them without using the ‘record’ function in Excel, but they always save me lots of time in my initial data sorting and I continue to learn different things that they can be used for all the time.

Macro recorder (from

If-then formulas are useful if you have data that you want to examined conditionalised on some other data.  For me this is usually along the lines of “If the response correct, and the participant gave a Remember response (as opposed to Know, Familiar, or Guess), what was the reaction time for that recognition decision?”  The recognition accuracy might be on one spreadsheet, the Remember data point on another, and the reaction time on a third.  This is where you can used nested if-then formulas: IF response is correct THEN look at ‘RKFG responses’, IF ‘RKFG response’ is Remember THEN put the reaction time value in this cell.  Like macros, formulas can also be kinda tricky – especially if you are nesting a lot of functions – you have to put the right number of ((( ))) around everything. But Excel will tell you when you’ve made a mistake – though often it doesn’t suggest where exactly you’ve gone wrong.

I don’t think Psychology undergraduates are taught enough about Excel (in fact I’m not sure most of them are taught anything about Excel except maybe how to make graphs?) – the focus in University statistics courses being on using SPSS from data entry to final analysis.  But writing script to perform if-then and macro functions in SPSS involves learning a lot more about writing scripting language than it does to perform those tasks in Excel.  Also, one of the beautiful things about Excel is that formulas remain live.  If you calculate a value using a formula you can still see the formula, you can check that the formula is correct, Excel will even highlight all the cells that are involved in your formula in different colours for you! (I ♥ colour-coding anything) To check your formulas in SPSS you have to remember to save all your syntax – and then you have to go back through it to find the bit that refers to that particular calculation.  No colour-coding in SPSS.

Macros and nested if-then formulas might be a bit too high-level for Undergraduate statistics classes, but a basic knowledge of Excel and a little hint at what macros and formulas are able to do, would be a useful starting point I think.  Then those students who go on to do a Masters or PhD would at least have a little grounding in the basics of Excel and would be aware that there are more cool things that Excel can do.

Nested if-then formulas in Excel

I was taught to use macros and formulas by my PhD supervisor.  I remember the sense of awe and excitement I had sitting in front of his computer when he showed me how to write/record/run them and explained how much time they could save and how many different things I could do with them.  In my last postdoc and my current one I have been able to pass some of this knowledge on to other students.  This makes me feel warm and fuzzy and I really enjoy passing on this knowledge…

Obviously I don’t only do it because it makes me feel warm and fuzzy(!), I pass it on because I think macros and formulas could save them time in what they are trying to do with their data.  And that, in general, data sorting is much easier in Excel than in SPSS.  But I do enjoy the ‘passing-on’ element of it.  The idea that this information was passed from my mentor to me and now I’m getting to pass it on.  It makes me feel like a proper academic.

[Nb. I’m sure there are competing data processing packages out there that have the same formula and macro abilities as Excel, I was just taught how to do these things in Excel and I like it’s functionality. And I don’t know what other programmes are out there and what cool stuff they can do, for that, my apologies.]


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s