So THIS is what those $ signs are about in Excel...

Data from a multiple choice test shows the option candidates chose (1, 2 or 3). I've used a formula to calculate by reference to a row of the keys how many questions were answered correctly. But when I come to do regression analysis, the option chosen by each candidate is meaningless unless referenced to the key - it's just a number 1, 2 or 3... 

So, what we need (for now) is a binary system where 1= chose key, and 0= chose a distractor. I add in another column, and get the right answer in the first cell with:

=COUNTIF(C3,C2).  

But when I drag that down the whole column, (and, mind, it's candidates n=540, here, don't want to do anything manual on each of them), the second value in the formula, which I want to stick at C2 (the key) will keep shifting, C3, C4, C5 etc. No bloody good. 

Thanks to the nice man who does the Not Just Numbers blog for this post which showed me how to do it. To preserve the value of the cell in a formula which you want to keep fixed, put a dollar sign in front of both the column and row signifier, so that in my formula, it looks like this: 

=COUNTIF(C3,$C$2)

And then you can drag the bugger down the entire array. The first value, (C4 in my case) shifts as you'd want it to, but our old friend C2 stays just as she is. Lovely. 

[NB: I saw reference elsewhere to highlighting the bit of the formula you need to preserve and then hitting F4 - but this doesn't seem to work on a Mac. You need dollars to do it.]

Now, where was I? Ah yes, linear regression... Getting there.