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:
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:
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.