Steve (akicif) wrote,

  • Mood:

Quiz analysis - Excel?

Does anyone know anything about using Excel for qualitative (as opposed to numeric) analysis, please?

I'm looking at designing a quiz or two, but would like to be able to analyse the results in slightly more depth than Excel seems to want to let me.

Say I have two columns of responses, and each column has about four possibilities. It's easy enough to do a chart for each column showing the numbers for each response "manually", by using =COUNTIF(A1:A25,"clicky thing!") or similar and sticking these in a column of their own - or I can be a little more clever and have the choices in a column of their own (B1 has "Clicky Thing!", B2 has "Shiny!", B3 has "Look! the Winged Victory of Samothrace") and have =COUNTIF(A$1:A$25,B1) and so on. [Side thing: if there was a function that would take all the values in A and return just the four unique values and put them in B, I could save myself even more typing]

But anyway, I can do that stuff more or less manually, and it doesn't return much more than the charts from the LJ polls.

What I'd like to do is be able to automagically compare two sets of answers, so if I had a second question (D contains the answers, E contains "Impeccable manners", "Excellent taste" and "His sombrero and flippers", with the matching frequency counts in F), I could chart either all the answers to the first question given by respondents who consider the true characteristic of a gentleman to be his elegant dress sense, or similarly chart all answers to the second question made by magpie-types.

It's all doable slowly and manually with lots of sorting of columns and chopping bits out here and there, but the time taken seems to go up rather quickly with the number of questions, and while I have Grandiose Plans, I'm also Dead Lazy.

Oh, and I know I said this was qualitative, but are there any numerical measures of correlation that I should consider applying here?

Previously suggested solutions have involved nesting or concatenating COUNTIFs (doesn't seem to work), using SUMIF (only works for numbers), or doing the whole thing with mySQL instead (I'm not sure I know enough to do that, but it would have the advantage that I could -in theory- also do all the graphing with the GD package in PHP (which I've been meaning to learn for a while now).

  • My tweets

    Wed, 22:19: 2016 Holyrood numbers Party Seats AMSeats ConstSeats AMvotes ConstVote AMCost ConstCost TotCost SNP 63 4 59 954k 1060k 238k 18k 32k…

  • My tweets

    Tue, 16:55: One for Doctor Who fans. Why does the phrase "resistance is useless" peak during Madame de Pompadour's time at the court of Louis…

  • My tweets

    Mon, 10:27: So, should it really be the 8th of *May* that schools re-open on? Johnson betrayed by his classical education again, perhaps? "In…

  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.