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. 

Comments

Post a Comment

Popular posts from this blog

Leech or flatworm? Ants and Swiss Chard

Image
For the 2nd time in a week or so, (1st time regarding dwarf beans ), I'm following Alys Fowler's advice, this time with regard to Swiss chard .  Long story short, after midsummer plants which often bolt if planted in spring will resist the impulse to do so, probably because of shortening days; (I say shortening, but in Glasgow, in July, we're still getting 18 hours of daylight).   I got a packet of Bright Lights , at a good price, (another reason to wait until July before sowing, lots of bargains, I've noticed). But where to sow them? Most of the 2nd bed is under a tarpaulin, but it wasn't quite big enough to cover the whole bed, so the last 5ft or so went under a sheet of plastic, (in the background of the photo in this post, here .) Transparent plastic was a foolish idea, weeds were growing underneath it, so I covered most of it with sheets of metal from the old shed. I decided to leave the tarp, but use the area beneath the plastic for the chard. Who-ah there...
Read more

"Chevy Chase. Fuck ever happen to him?"

Image
As Paulie Walnuts said in The Sopranos. It got me thinking. As it happens, he's been working steadily , but you can see what Paulie means. It's like that with film and television actors: you don't see them in anything for a year or two, and you wonder if they're dead or perhaps on a lengthy charlie and booze session. Or maybe doing proper acting on the stage? Only appearing on the telly gives them meaning to us. That video with Paul Simon was on one of the cable music channels the other day. It always makes me smile. As for Paulie and the rest of the crew, the clock's ticking. Of course, America already knows how things finish, but we don't. My mate Ian was telling me about a friend of his, visiting the States a few weeks ago, flicking through the channels in his hotel bedroom, he hit on a repeat of THE final episode, just beginning. What a quandry! He watched it, as it goes, but kept shtum when he came home.
Read more

How many pallets can you fit in the back of a Fiat 500?

Image
At least 1. Maybe I could have got 3 altogether, at a pinch. Also 200+ gorse seeds, which have begun to look in danger of damping off, so I'm thinking they'll be better off at the allotment in the fresh air. I'm worried about slugs, snails and woodpigeons, though, so I put them in a wheelbarrow, covered with a net. Also this morning, planted a row of beetroot detroit.
Read more